Splitting IP Addresses on Middle Octets

[ruezo]

Hello Everyone,

I don’t know if this can be pulled off with VBA or not, but I putting the question out there.

I have a list of IP Addresses with a convention related to the to the location number. The middle two octets represent a four digit number associated with the location, anywhere from 0000 to 9999. Octets with a single number are interpreted as two digits with a leading zero. So, a location with IP Address 172.1.1.1 is a device at location 0101. A location with IP address 172.50.10.1 is a device at location 5010 and so on. All of the IP addresses are all in column A. I need to split the IP addresses up into two columns. One column B for those from 0000 to 5000, and the other column C for those from 5001 to 9999.

How can I go straight from the main list in A to splitting them into B and C without having to use the “Text to Columns” method. Right now I have to use the Text to Columns method, then do a VALUE on a CONCATENATE on the LEN of the two middle Text to Columns inside a IF conditional.

For addresses from 0000 to 5000 (Formula in Column F)
=IF(VALUE(CONCATENATE(IF(LEN(C1)=1,"0"&C1,C1) & IF(LEN(D1)=1,"0"&D1,D1)))<5000, A1, "Null")

For addresses from 5001 to 9999 (Formula in Column G)
=IF(VALUE(CONCATENATE(IF(LEN(C1)=1,"0"&C1,C1) & IF(LEN(D1)=1,"0"&D1,D1)))>5000, A1, "Null")

Current Process is:
1. Copy column of addresses to another place
2. Text to Columns on the copied location
3. Autofill the above formulas to same last row of the main static list of IP in Column A

Scratching my head to get to a more streamline solution directly from Column A into Columns B and C without the Text to Columns method.

Thank you for any suggestions you can provide.
Ruezo

[mike7952]

Give this a try

Please Login or Register  to view this content.