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"))
Subscribe to:
Post Comments (Atom)
9 comments:
Thank you!
You save me some time!
Thanks for sharing this.
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.
Thanks a lot. Works like charm
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
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)))))
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?
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])))
Post a Comment