Last Updated on August 3, 2018 by Dave Farquhar
Maybe I’m the only one who ever has to do this, but here’s how to remove the last octet of an IP address in Excel using one (relatively) simple formula, not a series of calculations.
Given a list of IP addresses in column A, paste this formula into any other column and fill down:
=LEFT(A2,FIND("~",SUBSTITUTE(A2,".","~",3))-1)
By copying the values in that column into another tab and deduplicating, I can figure out pretty quickly what network ranges I scanned successfully with a tool like Tenable.io or Qualys. And by using a pivot table using column B for the rows and a count of column B for the values, I can quickly figure out how many systems I reached in each range.
I use a similar trick to extract MAC addresses out of a blob of data in a single column in vulnerability data. Excel’s string functions let you do some slick things once you learn how to use them.
David Farquhar is a computer security professional, entrepreneur, and author. He started his career as a part-time computer technician in 1994, worked his way up to system administrator by 1997, and has specialized in vulnerability management since 2013. He invests in real estate on the side and his hobbies include O gauge trains, baseball cards, and retro computers and video games. A University of Missouri graduate, he holds CISSP and Security+ certifications. He lives in St. Louis with his family.