Looking for a formula that will convert IP address for sorting

[metelcom]

I want a formula that will create an IP address that can be sorted properly

example:

Row A Row B
11.23.45.678 011.023.045.678

 

[Don Guillett]

more examples

 

[Tyro]

He's looking for a method to change IP addresses into 12 character dotted
quads with leading zeros. All addresses will be in the form nnn.nnn.nnn.nnn
with leading zeroes in each group of 3. So, 1.22.33.4 becomes
001.022.033.004. Probably best handled by VBA

 

[Gord Dibben]

More detail please and note: columns are A,B,C,D etc. across the top

Rows are 1,2,3,4,5 etc. down the side.

Your data is in columns or rows?


Gord Dibben MS Excel MVP

 

[Don Guillett]

Sub leadingzeros()
lr = Cells(Rows.Count, "j").End(xlUp).Row
For Each c In Range("j2:j" & lr)
Select Case InStr(c, ".") - 1
Case 1: n = "00"
Case 2: n = "0"
Case 3: n = ""
Case Else
End Select
c.Value = n & c
Next c
End Sub

 

[Roger Govier]

Hi

One way, assuming the IP addresses are in column A and the other columns on
sheet are empty

Copy Column A to Column B
Mark column B>Data>Text to Columns>Next>Other delimiter>choose "." (without
the quotes)>Finish
You will now have the 4 components of the IP address split into columns
B,C,D and E
In F1 enter
=TEXT(B1,"0000")&"."&TEXT(C1,"0000")&"."&TEXT(D1,"0000")&"."&TEXT(E1,"0000")
Copy down Column F as far as required.
Mark column F>Copy>Move cursor to B1>Paste Special>Values
Delete columns C:F

Sort your data on Column B

 

[joeu2004]

I want a formula that will create an IP address that can be sorted properly
example:
    Row A                          Row B
11.23.45.678              011.023.045.678

First, "678" is not a valid octet of an IP address. Anyway....

Copy-and-Paste Special Value the range of cells in column A into
column B, then use the following macro (making sure the range in B is
still selected):

Sub convertIP()
For Each c In Selection
ip = Split(c.Value, ".", 4)
For i = 0 To 3
Select Case Len(ip(i))
Case 1: ip(i) = "00" & ip(i)
Case 2: ip(i) = "0" & ip(i)
Case Else
End Select
Next i
c.Value = Join(ip, ".")
Next c
End Sub

If you are not familiar with using macros, to enter the macro, press
alt+F11, click on Insert>Module, then cut-and-paste the the macro text
above into the editing window. Return to the Excel worksheet. After
doing the cut-and-paste into column B, press alt+F8, select convertIP,
and click Run.

Caveat emptor: The above macro code is not bullet-proof. It gets
errors if the original IP address does not have at least 3 dots, for
example.

 

[joeu2004]

use the following macro (making sure the range in B is still selected):

On second-thought, I think this is easier to use as a function. Enter
the function below according to the instructions in my previous
posting. Then, if A1 contains the IP address in dot-notation, put the
following into B1:

=convertIP(A1)

The function is:

Function convertIP(c) As String
ip = Split(c, ".", 4)
For i = 0 To 3
Select Case Len(ip(i))
Case 1: ip(i) = "00" & ip(i)
Case 2: ip(i) = "0" & ip(i)
Case Else
End Select
Next i
convertIP = Join(ip, ".")
End Function