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.
Recommended Productivity Tools
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?
The Text function will add zeros in front of numbers, and make the new numbers with leading zeros in certain length.
Step 1: Enter the formula =Text (A1, "00000") in a blank cell which is adjacent to the data cell.
Step 2: Then press Enter key, and select cell C1 drag the fill handle across the range that you want to fill. Then you will view all the numbers in A1:B6 are copied and pasted to C1:D6 with leading zeros. And each numbers contains 5 digits.
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.
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.
Step 1: Enter the formula =Concatenate ("000", A1) in a blank cell and press Enter key. In this case, we enter the formula in Cell C1.
Step 2: Click the Cell C1, and drag the fill handle across the range that you want to fill. Then you will view numbers in A1:B6 are pated to C1:D6, and each number contains three leading zeros.
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 includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.
Step 1: Select the range what you will work with.
Step 2: Right click the range, and select Formal cells from context menu. In the Format Cells dialog box, click the Text option in Category list under Number tab. Click OK.
Step 3: Click Kutools > Text > Add Text.
Step 4: In the Add Text dialog box, enter zeros in the Text box, and select the Before first character option.
Step5: And then click OK or Apply. Then all cells are added leading zeros in the selection.
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 120 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months agoWorked well for me. Thanks alot.
To post as a guest, your comment is unpublished.· 7 months agoHey, i was wondering how to put text in-front of a number with 0 decimal places.
ie. it comes up as " Sp3.145255246345 " and i want it to show " Sp3"
To post as a guest, your comment is unpublished.· 8 months agoUse 0&
example: =0&0&A1 (A1 is cell in which 00 is to be added as prefix)
To post as a guest, your comment is unpublished.· 9 months agoI need to add zeros to the front of the ID (know how to add leading zeros) but some need 6 and some need 5 zeros. Is there a formula to make sure I have a 10 digit number when I'm done?
To post as a guest, your comment is unpublished.· 9 months agoCMD & BLAKE, You are absolute whizzkids! :-)
- ← Previous
- Next →