On Thu, 20 Dec 2007 10:21:46 -0600, "Tim Kuhn" wrote:

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