Fomat cells for IP address including leading Zeros

[Pete]

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

 

[Guest]

try 000","000","000","000
with 0 instead of #
however this will only give you the leading zeros in the fisrt section, all
the rest need the zeros added
if you have the sections in different cells (A1!)you coul duse
=text(A1,"000")&","&text(B1,"000")&","&text(C1,"000"&","&text(D1,"000")
this will give you the leading zeros for each section

 

[Ron Rosenfeld]

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 worksheet 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

 

[Guest]

come to think of it, change the "," to "." everywhere in the formula

 

[Ron Rosenfeld]

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

Another method:

With your URL in A10

Select A10
Select Data/Text to Columns/Delimited
Other "."

That will split the URL into A1010

Then use this formula:

=TEXT(A10,"000\.")&TEXT(B10,"000\.")&
TEXT(C10,"000\.")&TEXT(D10,"000")


--ron

 

[Pete]

Ron,

Thank you. The worksheet f(x) workes perfectly. Forgive my
ignorance: With the VB UDF (UDF?), I assume I add this as a module.
I expected to see this as a macro. Obviously, this is not the case.
Perhaps I shpould read up on VB basics, unfortunately my work schedule
does not permit it, and the last thing I want to do aat home is learn
VB!
Are you willing to give me a quick explanation, or am I deserving of a
RTFM?

Thank you again. This will save me a great deal of time. With your
permission, I will share this with other co-workers.

Pete

 

[Ron Rosenfeld]

Ron,

Thank you. The worksheet f(x) workes perfectly. Forgive my
ignorance: With the VB UDF (UDF?), I assume I add this as a module.
I expected to see this as a macro. Obviously, this is not the case.

UDF = User Defined Function

Since I wrote a function, it would show up in the function wizard under
user-defined functions. In that way, it can accept arguments (the unformatted
URL string) and return the desired result (formatted URL string).

Had I written a Sub, then it would show up as a macro.

There are a number of ways to solve your problem -- I only offered a few.

Please also see my other message, where I discussed the Text-to-columns wizard
and a simpler formula.

Thank you again. This will save me a great deal of time. With your
permission, I will share this with other co-workers.

Of course you have my permission. Anything posted here is "public domain".
--ron