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 has written professionally about computers since 1991, so he was writing about retro computers when they were still new. He has been working in IT professionally since 1994 and has specialized in vulnerability management since 2013. He holds Security+ and CISSP certifications. Today he blogs five times a week, mostly about retro computers and retro gaming covering the time period from 1975 to 2000.