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 zero to three digits in every segment as below screenshot shown. 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, 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 Enter key, drag fill handle down to the cells needed 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 address.
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 which you will place the calculated result, type this formula =IP(A1), press Enter key, and drag fill handle down to the cells you need. See screenshot:
 |
 |
 |
Relative 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
