Show IP addresses with leading zeros

treyfoxx

New Member
Joined
Dec 16, 2009
Messages
1
Does anyone know how I can format a cell to always add zeros to IP addresses?

ex.

change 69.21.13.222 to 069.021.013.222

The cells data is being populated from other spreadsheets using a VLOOKUP. I just need the leading zeros.

Thanks
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Welcome to the Board!

This is one way, but it could probably be shorter:

Excel Workbook
FG
121.5.4.12001.005.004.012
1369.21.13.222069.021.013.222
Sheet1
Cell Formulas
RangeFormula
G12=TEXT(LEFT(F12,FIND(".",F12)-1),"000.")&TEXT(MID(F12,FIND(".",F12)+1,FIND("~",SUBSTITUTE(F12,".","~",2))-FIND(".",F12)-1),"000.")&TEXT(MID(F12,FIND("~",SUBSTITUTE(F12,".","~",2))+1,FIND("~",SUBSTITUTE(F12,".","~",3))-FIND(".",F12)-1),"000.")&TEXT(MID(F12,FIND("~",SUBSTITUTE(F12,".","~",3))+1,LEN(F12)-FIND(".",F12)-1),"000")

Hope that helps.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Here's a UDF that will do that:

Code:
Function ip(r As String) As String
Dim t, x As Long
t = Split(r, ".")
For x = LBound(t) To UBound(t)
    t(x) = Right("000" & t(x), 3)
Next
ip = Join(t, ".")
End Function

Excel Workbook
AB
169.21.13.222069.021.013.222
2100.0.9.255100.000.009.255
Sheet1
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welcome to the board

A formula solution:

=TEXT(LEFT(A1,FIND("#",SUBSTITUTE(A1,".","#",2))-1),"000.000\.")&TEXT(RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,".","#",2))),"000.000")
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

That formula makes the first 21, 210 not 021. Any 1 or 2 digit number after the decimal point in the 2 blocks is going to have this problem
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
That formula makes the first 21, 210 not 021. Any 1 or 2 digit number after the decimal point in the 2 blocks is going to have this problem

Thank you HOTPEPPER

Now that you pointed it out I see it's really dumb! :(

Sorry treyfoxx, I'm leaving and I didn't test it (or think it) properly. I'm glad you already have a formula and a vba solution.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Ok, I'm back

Here is my new formula:

=TEXT(SUMPRODUCT(MID(A1,FIND("#",SUBSTITUTE("."&A1,".","#",{1,2,3,4})),FIND("#",SUBSTITUTE(A1&".",".","#",{1,2,3,4}))-FIND("#",SUBSTITUTE("."&A1,".","#",{1,2,3,4})))*10^{9,6,3,0}),"000\.000\.000\.000")

Here some tests:

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">1.5.4.12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">001.005.004.012</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">69.21.13.222</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">069.021.013.222</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">0.0.0.0</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">000.000.000.000</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">255.255.255.255</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">255.255.255.255</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">01.124.4.47</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">001.124.004.047</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet3</td></tr></table>
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
And an UDF:

Code:
Function IP3Digits(s As String)
IP3Digits = Format(Evaluate("(((" & Replace(s, ".", ")*10^3+")), "000"".""000"".""000"".""000")
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,880
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top