Remove the last octet of an IP address in Excel

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this:
WordPress Appliance - Powered by TurnKey Linux