How to pad IP address with zero in Excel?

When receiving a sheet which contains some IP addresses with two digits in some segments, you may want to pad the IP address with zeros so that every segment has three digits, as shown in the screenshot below. In this article, I introduce some tricks to quickly pad IP address in Excel.

Pad IP address with zero by formula

Pad IP address with zero by Defined Function

Pad IP address with zero by formula

Here is a long formula that can help you quickly pad IP address with zero.

Select a blank cell, and type this formula:

=TEXT(LEFT(A1,FIND(".",A1)-1),"000.")&TEXT(MID(A1,FIND(".",A1)+1,FIND("~",SUBSTITUTE(A1,".","~",2))-FIND(".",A1)-1),"000.")&TEXT(MID(A1,FIND("~",SUBSTITUTE(A1,".","~",2))+1,FIND("~",SUBSTITUTE(A1,".","~",3))-FIND(".",A1)-1),"000.")&TEXT(MID(A1,FIND("~",SUBSTITUTE(A1,".","~",3))+1,LEN(A1)-FIND(".",A1)-1),"000")

A1 is the IP address you use. Press the Enter key, and drag the fill handle down to the cells that need this formula. see screenshot:

Pad IP address with zero by Defined Function

If you are familiar with VBA code, here is a code that can also help you to pad IP addresses.

1. Press Alt + F11 keys to enable Microsoft Visual Basic for Applications window, click Insert > Module to create a new module. See screenshot:

2. In the Module script, paste below code to it, and then save the code and close the VBA window. See screenshot:

VBA: Pad IP Address

Function IP(Txt As String) As String
'UpdatebyExtendoffice20170725
    Dim xList As Variant
    Dim I As Long
    xList = Split(Txt, ".")
    For I = LBound(xList) To UBound(xList)
        xList(I) = Format(xList(I), "000")
    Next
    IP = Join(xList, ".")
End Function

3. Go to a blank cell where you want to place the calculated result, type this formula =IP(A1), press the Enter key, and drag the fill handle down to the cells you need. See screenshot:

Related Articles

• How to extract a string from IP address in Excel?
• How to add the same email address to a list of names in Excel?