Saturday, August 11, 2012

Baby Steps

The past few weeks have been particularly challenging, we've not been able to do any work or move the boat closer to home; however, that doesn't mean that nothing has been done.

Last week Atlantic Spar and Rigging came down and completed a rig inspection as was required by the insurance company to cover the rig and gave us a clean bill of health with the exception of a few minor items. They also tuned up the rig, I'm looking forward to the next sail.

Hartge Yacht Harbor also did some work on the anchor locker, reglassing the compartment and then painting both the anchor locker and sail locker.

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