0 Replies Latest reply on Dec 5, 2012 9:48 AM by tdavenport

    Convert list of IP Addresses to Full Octet

    tdavenport Specialist

      I have had a need of this on several occasions when trying match IP Addresses and IP Address Ranges. When presented with a list of IP Addresses in a spreadsheet, they may not be formatted in the full octect as they are in LANDesk. This formula will quickly convert a list of IP Addresses to the full octet. It can be very helpful at times. I can't claim credit for it. The post can be found here: http://www.excelbanter.com/showthread.php?t=145814

       

      Here's the formula:

       

      =TEXT(LEFT(A1,FIND(".",A1)-1),"000") & "."

      &TEXT(MID(A1,FIND(".",A1)+1,-1+FIND(

      ".",A1,FIND(".",A1)+1)-FIND(".",A1)),"000")&"."

      &TEXT(INT(MID(A1,FIND(".",A1,FIND(".",A1)+1)

      +1,255)),"000")&"."&TEXT(MID(SUBSTITUTE(

      A1,".",CHAR(1),3),FIND(CHAR(1),

      SUBSTITUTE(A1,".",CHAR(1),3))+1,3),"000")