How to Add or Pad Leading Zeros to Numbers or Text in Excel?
If you have a list of numbers or text in Excel and need to add leading zeros for consistent formatting or specific requirements, you might notice that typing them manually causes Excel to remove the zeros. Fortunately, Excel offers several ways to handle this issue. This guide will show you how to efficiently add or pad leading zeros to your data using various methods.
Method 1: Add or pad the leading zeros to numbers to equal certain length with Text function
Method 2: Add or pad the leading zeros to numbers to equal certain length with Format Cells function
Method 3: Add or insert the same number of leading zeros to numbers with Concatenate function
Method 4: Add or insert the same number of leading zeros to numbers and text with Kutools for Excel
Method 1: Add or pad the leading zeros to numbers to equal certain length with Text function
Suppose you have a range of data, and you need to add zeros in front of each cell to make the numbers the same length. How could you quickly add zeros before the numbers?
The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length.
Enter the formula =TEXT(A4,"000000") in a blank cell where you want to output the result, and then drag the fill handle down to the cells that you want to apply this formula, and all the numbers become a fixed six-digit character string, see screenshot:
Note: This Text function can not add leading zeros for text.
Method 2: Add or pad the leading zeros to numbers to equal certain length with Format Cells function
In Excel, the Format Cells feature also can help you pad the leading zeros to cell values to make them as same length, please do as this:
1. Select the cell values you want to pad leading zeros, and then right click, then choose Format Cells from the context menu.
2. In the Format Cells dialog box, under the Number tab, select Custom in the left Category pane, and then enter 000000 into the Type text box, (In this example, I will make the numbers as six-digit numbers, you can change it to your need.), see screenshot:
3. Then, click OK to close this dialog box, and the numbers in the original cells have been converted to the certain length numbers with leading zeros. See screenshot:
Method 3: Add or insert the same number of leading zeros to numbers with Concatenate function
If you want to insert specific digit of leading zeros into each number, for example three leading zeros for each number, you should try the Concatenate function.
Enter the formula =CONCATENATE("000",A4) in a blank cell, and then drag the fill handle down to fill this formula to other cells, and all numbers have been added three zeros before the numbers, see screenshot:
Method 4: Method 4: Add or insert the same number of leading zeros to numbers and text with Kutools for Excel
Functions or formulas may not be easy for you to remember and apply. Here is another tricky way - the Add Text utility of Kutools for Excel - which can help you insert leading zeros before numbers in selections quickly.
1. Select the range that you want to add zeros before the numbers or text.
2. Then click Kutools > Text > Add Text, see screenshot:
3. In the Add Text dialog box, enter the number of zeros with a leading apostrophe in the Text box, select the Before first character option, and then click the Ok button. Zeros will be added before the numbers as following screenshot shown:
The Add Text utility of Kutools for Excel supports us to add any specific text and characters before or after the original cell text. What's more, we can add specific text between original cell texts. No matter which settings you set, you can preview the cell changes in the Preview section. Click to know more about this utility.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Add leading zeros to numbers or text with Kutools for 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!