Skip to main content

How to Add or Pad Leading Zeros to Numbers or Text in Excel?

Author: Kelly Last Modified: 2024-12-16

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.

A screenshot showing an Excel worksheet with numbers missing leading zeros

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:

A screenshot demonstrating the result of using the TEXT function to add leading zeros in Excel

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:

A screenshot of the Format Cells dialog in Excel, showing the use of custom number formatting to add leading zeros

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:

A screenshot showing the result of applying Format Cells to add leading zeros in Excel


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:

A screenshot demonstrating the use of the CONCATENATE function to add leading zeros in Excel


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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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:

7

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

Kutools for Excel: Over 300 handy tools at your fingertips! Enjoy permanently free AI features! Download Now!

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!