Re: Fomat cells for IP address including leading Zeros

Tech-Archive recommends: Speed Up your PC by fixing your registry

On Fri, 08 Jun 2007 08:20:51 -0700, Pete <pmarion@xxxxxxxxx> wrote:

I replied to the following thread: http://tinyurl.com/2ky6vm, but it
was a month old and I've gotten no replies. Therefore, I am creating
this new post to address the matter.

This has been asked previously, but I have never seen a response that
I can understand or apply. I'm using MS Excel 2003. I frequently
need to enter IP addresses into a product called Remedy, and this
product requires that entries contain 12 numerals in IP Address
entries. It would be a huge timesaver to be able to take my lists of
IP Addresses and paste the segment IP addresses into worksheets that
convert them to Remedy Format (with Leading Zeros where necessary.) I
tried the number format ###"."###"."###"."### but this will not add
the leading zeros to IP addresses entered without them.

Is there a means to accomplish this without manually entering the
leading zeros? (unrealistic)
I have minimal scripting experience. Thanks in advance.

Pete Marion
Commonwealth of Pennsylvania
Information Systems
Technology Engineering
Security Architecture

Number formats will not work because a string with multiple "dots" is not a
number.

It's easier with a VBA UDF, but as a work*** function, with your URL in A1 in
the form of, let us say,

12.3.45.81

Try:

=TEXT(LEFT(A1,FIND(".",A1)-1),"000") & "."
&TEXT(MID(A1,FIND(".",A1)+1,-1+FIND(
".",A1,FIND(".",A1)+1)-FIND(".",A1)),"000")&"."
&TEXT(INT(MID(A1,FIND(".",A1,FIND(".",A1)+1)
+1,255)),"000")&"."&TEXT(MID(SUBSTITUTE(
A1,".",CHAR(1),3),FIND(CHAR(1),
SUBSTITUTE(A1,".",CHAR(1),3))+1,3),"000")

This will return:

012.003.045.081

which is what I think you want.


As a VBA UDF:

==================================
Option Explicit

Function FormatURL(URL As String) As String
Dim sURL
Dim i As Long
sURL = Split(URL, ".")

'simple check only for valid URL
'could make this more comprehensive
If UBound(sURL) <> 3 Then Exit Function 'not a valid URL

For i = 0 To 3
FormatURL = FormatURL & Format(sURL(i), "000\.")
Next i

'remove last "."

FormatURL = Left(FormatURL, 15)

End Function
=================================
--ron
.

Quantcast