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.
Recommended Excel Productivity Tools
Here is a long formula that can help you quickly pad IP address with zero.
Select a blank cell, type this formula,
A1 is the IP address you use, press Enter key, drag fill handle down to the cells needed this formula. see screenshot:
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:
|Kutools for Excel's Extract Email Address utility can quickly extract all email addresses from text strings to another location.|
- How to extract a string from IP address in Excel?
- How to add the same email address to a list of names in Excel?