How To Add or Pad Leading Zeros To Numbers or text In Excel?
If you have a list of numbers that you need to add or pad some leading zeros before the numbers, when typing the zeros manually, they will be removed 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.
Supposing you have a range of data, and you need to add zeros in front of each cells to make the numbers as same length. How could you quickly add the 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:
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 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 30 days.|
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, and select the Before first character option, then click Ok button, and the zeros have been 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 includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Download the free trial now!
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 years agoOk so what if your field has A1234 and you want to add a 0 in front of the A how do you do that?
- To post as a guest, your comment is unpublished.· 8 years agothanks....awsum tools
- To post as a guest, your comment is unpublished.· 8 years agoThanks, Its very helpful for me.
- To post as a guest, your comment is unpublished.· 8 years agoWHAT ABOUT CASES WHERE YOU NEED ALL THE DATA TO HAVE SAME NO OF DIGITS BUT THE AVAILABLE ARE VARIABLE. E.G CELL ONE HAS 878, CELL TWO HAS 1278 AND WANT ALL TO HAVE SIX DIGITS, THIS MEANS ADDING ZEROS TO EACH BUT NOT SAME NUMBER OF ZEROS
- To post as a guest, your comment is unpublished.· 5 years agoall you need to do is ' before the cero and than whatever number you want
- To post as a guest, your comment is unpublished.· 4 years agoI too found this to be the easiest way! Simply used the "Find & Replace" to edit the entire document. Thank you!
- To post as a guest, your comment is unpublished.· 5 years agoThank you Jorge, that's the simplest and quickest solution to add zeros in front of numbers .
- To post as a guest, your comment is unpublished.· 6 years agoIf statements can cover multiple situations if that is needed
- To post as a guest, your comment is unpublished.· 8 years agoUse the =Text("000",A1) formula above. It will do that, I just tried it with great success.