Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to add/insert leading zeros to numbers or text in Excel?

Supposing you need to input some zip codes or phone numbers in cells of Excel, then you will find Microsoft Excel removes the leading zeros by default. This article is going to show you some tutorials about how to add leading zeros to numbers or text in ranges of cells in Excel.

Add/ insert leading zeros to numbers with Text function

Add/ insert leading zeros to numbers and text with Concatenate function

Add/ insert leading zeros to numbers and text with Kutools for Excel


Add or insert leading zeros to numbers or text strings in Excel:

With Kutools for Excel's Add Text feature, you can quickly add text before, after or certain position for a list of cell data as you need.

doc insert leading zeros 6

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Add/ insert leading zeros to numbers with Text function


Supposing you have a range of data, and you need to add zeros in front of each cells as following screenshots shown. How could you quickly add the zeros before the numbers?

doc insert leading zeros 1

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 (A2, "00000") in a blank cellwhere you want to output the result, and then darg the fill handle down to the cells that you want to apply this formula, and all the numbers become a fixed five-digit character string, see screenshot:

doc insert leading zeros 2

Tips: 1. As they are formulas, so when you need to copy and paste them to other places, you need to paste them as values.

2. This Text function can not add leading zeros for text.


Add/ insert leading zeros to numbers and text 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", A2) 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:

doc insert leading zeros 3


Add/ insert leading zeros to numbers and text with Kutools for Excel

Functions or formulas may be not easy for you to remember and apply. Here is another tricky way, the Add Text utility of Kutools for Excel, to help you insert leading zeros before numbers in selections.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

1.  Select the range what you will work with, and then select Text from the Number Format drop down list under the Home tab to format the numbers as text formattting, see screenshot:

doc insert leading zeros 4

2.  Then click Kutools > Text > Add Text, see screenshot:

doc insert leading zeros 5

3.  In the Add Text dialog box, enter zeros in the Text box, and select the Before first character option, then click Ok button, and the zeros have been added before the numbers as following screenshot shown:

doc insert leading zeros 6

Click to Download Kutools for Excel and free trial Now!

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.


Keep formula cell reference constant with Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    superstar · 1 months ago
    В примере ошибка, нужно ставить не запятую, а точку с запятой: "= Текст (A2, "00000")"
  • To post as a guest, your comment is unpublished.
    Narcisse · 2 months ago
    thank you, very helpful
  • To post as a guest, your comment is unpublished.
    Bernice · 10 months ago
    I was wondering how i would write phone numbers in excel, but there was no zeros appearing. And also the phone numbers are in one column like

    column D
  • To post as a guest, your comment is unpublished.
    Rashida · 1 years ago
    Thank you for sharing..it's really use for me.
  • To post as a guest, your comment is unpublished.
    Shirley · 1 years ago
    Thank you for helping me out. You saved my day!