Show IP addresses with leading zeros

[treyfoxx]

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

 

[schielrn]

Welcome to the Board!

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

Excel Workbook
	F	G
12	1.5.4.12	001.005.004.012
13	69.21.13.222	069.021.013.222
Sheet1

Cell Formulas
Range	Formula
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]

Hi, welcome to the board!

Here's a UDF that will do that:

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
	A	B
1	69.21.13.222	069.021.013.222
2	100.0.9.255	100.000.009.255
Sheet1

 

[pgc01]

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]

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]

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]

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]

And an UDF:

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