Separate IP Address Octets in Excel

If you find yourself with a spreadsheet containing hundreds of servers, and there’s an IP Address Column, you may want to create 4 additional columns, each containing each of the four octets of the IP Address.

This may be useful for grouping large numbers of hosts in a spreadsheet by subnet.

And this may be useful in grouping together servers in legacy infrastructure by application where that information doesn’t exist, since deployments of solutions across multiple servers are often grouped together in a range of IP addresses allocated to the projects by the Network admin.

These formulae are used to separate out each of the four octets.

A2:=LEFT(A1,FIND(".",A1)-1)
A3:=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1)
A4:=MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-(FIND(".",A1,FIND(".",A1)+1)+1))
A5:=MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)+1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))
Facebooktwitterredditpinterestlinkedinmail

2 Replies to “Separate IP Address Octets in Excel”

  1. What if I needed to strip an ip with cidr into 4 octets and a 5th column containing the cidr? with the / and with out the /.

    Example: 192.168.100.1/24
    A1= 192.168.100.1/24
    A2=192
    A3=168
    A4=100
    A5=1
    A6=/24
    A7=24

Leave a Reply

Your email address will not be published. Required fields are marked *

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