Wednesday, August 1, 2012

Something different - manipulating IP address strings in Excel

This is not normally the sort of thing I post on this blog but a quick search didn't find anything quite like it and it may be useful to someone else.

The following are a series of excel formulas that break down an IP address and allow you to manipulate the octets within the formula. The other examples I found all use vba, etc. These formula, although not terribly elegant, should be reasonably easy to deal with.


In these examples the field D6 is the target IP address:

# The length of the IP string and the first octet delineator
=LEN(D6)
=FIND(".",D6)

#Octet 1
=LEFT(D6,FIND(".",D6)-1)

#Octets 2-4
=RIGHT(D6,(LEN(D6)-FIND(".",D6)))

#Octet 2
=LEFT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))-1)

# Location of the second "."
=FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))

#Length of last three octets
=LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))

#Lenght of the last two octets
=LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))

#Octets 3-4
=RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6)))))

#Location of the third "."
=FIND(".",RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))))

#Octet 3
=LEFT(RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))),FIND(".",RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))))-1)

#Octet 4
=RIGHT(RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))),(LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))))))

#Last Octet with incremented (by 1) host IP
=TEXT((VALUE(RIGHT(RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))),(LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(RIGHT(D6,(LEN(D6)-FIND(".",D6))),LEN(RIGHT(D6,(LEN(D6)-FIND(".",D6))))-FIND(".",RIGHT(D6,(LEN(D6)-FIND(".",D6)))))))))+1),"0")

#Reassembly of the new IP address, the number +2, near the end of the string is the amount the host address will be incremented by
=CONCATENATE(LEFT($D6,FIND(".",$D6)-1),".",LEFT(RIGHT($D6,(LEN($D6)-FIND(".",$D6))),FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-1),".",LEFT(RIGHT(RIGHT($D6,(LEN($D6)-FIND(".",$D6))),LEN(RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6))))),FIND(".",RIGHT(RIGHT($D6,(LEN($D6)-FIND(".",$D6))),LEN(RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6))))))-1),".",TEXT((VALUE(RIGHT(RIGHT(RIGHT($D6,(LEN($D6)-FIND(".",$D6))),LEN(RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6))))),(LEN(RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT(RIGHT($D6,(LEN($D6)-FIND(".",$D6))),LEN(RIGHT($D6,(LEN($D6)-FIND(".",$D6))))-FIND(".",RIGHT($D6,(LEN($D6)-FIND(".",$D6)))))))))+2),"0"))

9 comments:

GeorgeG said...

Thank you!
You save me some time!

Stephen Hewitson said...

Thanks for sharing this.

Sam said...

Thanks, With this I think I can get rid of the manual steps of taking the ip address and separating it into a cell for each octet and then putting the first three cells back together so I can count IP's in a particular network. It is not bad for me but documenting the process for others is mind boggling.

hicham K said...

Thanks a lot. Works like charm

Rich Zarei said...

Thanks for this. I had been using LEN + LEFT + RIGHT for the past day trying to do exactly this. While I was close, certain octet patterns would give me mixed results. This vba script did the trick!

Thank you so much

me said...

I love this post, I was looking for just the value of the 4th octet and found a little different solution, Based upon what I learned using this post.
=RIGHT(D6,(LEN(D6)-(FIND(".",D6,(FIND(".",D6,FIND(".",D6))+(FIND(".",D6)+1)+!)))))
Basically this code takes the length of the octets adds 1 and finds the length to the net delimiter.

and then if all I want is the 4th octet removed I use the code
=LEFT(D6,VALUE(VALUE(FIND(".",D6,(FIND(".",D6,FIND(".",D6))+(FIND(".",D6)+1)+1)))))

Mark Bowen said...
This comment has been removed by the author.
Mark Bowen said...

An IP address of 172.19.2.21 makes this logic
=LEFT(D6,VALUE(VALUE(FIND(".",D6,(FIND(".",D6,FIND(".",D6))+(FIND(".",D6)+1)+1)))))

barf ... and I'm stumped as to why!

Help?

Drew Robertson said...

Second Octect
=TRIM(LEFT(SUBSTITUTE(MID([IP],FIND("|",SUBSTITUTE([IP],".","|",1))+1,LEN([IP])),".",REPT(" ",LEN([IP]))),LEN([IP])))

Third Octect
=TRIM(LEFT(SUBSTITUTE(MID([IP],FIND("|",SUBSTITUTE([IP],".","|",2))+1,LEN([IP])),".",REPT(" ",LEN([IP]))),LEN([IP])))

Forth Octect
=TRIM(LEFT(SUBSTITUTE(MID([IP],FIND("|",SUBSTITUTE([IP],".","|",3))+1,LEN([IP])),".",REPT(" ",LEN([IP]))),LEN([IP])))