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

more examples


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"metelcom" wrote in message
...
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

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


"Don Guillett" wrote in message
...
more examples


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"metelcom" wrote in message
...
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

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

On Sun, 6 Jan 2008 08:55:06 -0800, metelcom
wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"metelcom" wrote in message
...
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

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 BDataText to ColumnsNextOther delimiterchoose "." (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 FCopyMove cursor to B1Paste SpecialValues
Delete columns C:F

Sort your data on Column B

--

Regards
Roger Govier

"metelcom" wrote in message
...
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

On Jan 6, 8:55*am, metelcom
wrote:
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 InsertModule, 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.

On Jan 6, 10:54*am, I wrote:
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