Remove the last octet of an IP address in Excel

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:


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 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.

If you found this post informative or helpful, please share it!
%d bloggers like this: