Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
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:D!)you coul duse =text(A1,"000")&","&text(B1,"000")&","&text(C1,"00 0"&","&text(D1,"000") this will give you the leading zeros for each section "Pete" 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
On Fri, 08 Jun 2007 08:20:51 -0700, Pete 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 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
come to think of it, change the "," to "." everywhere in the formula
"bj" wrote: 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:D!)you coul duse =text(A1,"000")&","&text(B1,"000")&","&text(C1,"00 0"&","&text(D1,"000") this will give you the leading zeros for each section "Pete" 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
On Fri, 08 Jun 2007 08:20:51 -0700, Pete 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 Another method: With your URL in A10 Select A10 Select Data/Text to Columns/Delimited Other "." That will split the URL into A10:D10 Then use this formula: =TEXT(A10,"000\.")&TEXT(B10,"000\.")& TEXT(C10,"000\.")&TEXT(D10,"000") --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
On Jun 8, 12:05 pm, Ron Rosenfeld wrote: 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 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- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fomat cells for IP address including leading Zeros
On Tue, 12 Jun 2007 12:10:50 -0700, Pete wrote:
On Jun 8, 12:05 pm, Ron Rosenfeld wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I keep leading zeros and format my cells as text? | Excel Discussion (Misc queries) | |||
in excel, how do i get an average without including the zeros? | Excel Worksheet Functions | |||
How do I average a column without including zeros | Excel Discussion (Misc queries) | |||
Is it possible to show leading zeros for "number" cells? | Excel Discussion (Misc queries) | |||
save text field w/ leading zeros in .csv format & not lose zeros? | Excel Discussion (Misc queries) |