Number Conversion

[Tim Kuhn]

Hello

I currently have a worksheet with numbers that are formatted like IP Address
(XXX.XXX.XXX.XXX)

My problem is that some of the leading 0's are missing,
for example 10.4.100.200 should be 010.004.100.200

Is there any quick way to convert the list so that it fits
the format XXX.XXX.XXX.XXX, with any missing numbers being filled with 0's.

Regards
Tim

 

[Ron Rosenfeld]

Hello

I currently have a worksheet with numbers that are formatted like IP Address
(XXX.XXX.XXX.XXX)

My problem is that some of the leading 0's are missing,
for example 10.4.100.200 should be 010.004.100.200

Is there any quick way to convert the list so that it fits
the format XXX.XXX.XXX.XXX, with any missing numbers being filled with 0's.

Regards
Tim

It can be done with a complex formula, but I find using VBA much simpler in
this instance.

Make a backup.

Then select the cells, and run the VBA Macro below.

To enter the macro, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Then select your cells.
<alt-F8> opens the Macro Dialog box.
Select the FormatIP macro and RUN

=============================================
Option Explicit
Sub FormatIP()
Dim c As Range
Dim temp
Dim i As Long
For Each c In Selection
temp = Split(c.Value, ".")
For i = 0 To UBound(temp)
temp(i) = Format(temp(i), "000")
Next i
c.Value = Join(temp, ".")
Next c
End Sub
================================================

The routine, as written, does not check for invalid entries. That is easily
added if an issue.
--ron

 

[Dave Peterson]

Another way to do it is to use some helper columns and a few manual steps.

Say your list is in A1:A999
Insert 5 new columns to the right of column A (say B:F)

Then select column A.
Data|Text to columns
Delimited (by period)
and plop the results in B1 (B1:E999)

Then in F1, you can recombine the numbers:
=text(b1,"000.")&text(c1,"000.")&text(d1,"000.")&text(e1,"000")
And drag down to E999

Then select column E and
Edit|copy
paste over column A
and delete those helper columns (B:F).