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"))