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 2007/2010.

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

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

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

doc-add-leading-zeros1-2doc-add-leading-zeros2

 


Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

arrow blue right bubble Add/ insert leading zeros to numbers with Text function

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.

doc-add-leading-zeros3

Step 2: Then press Enter key, and select cell C1 drag the fill handle Description: Selected cell with 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 numbers. And each numbers contains 5 digits.

doc-add-leading-zeros4

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 numbers for text.


arrow blue right bubble 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.

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 Description: Selected cell with 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.

doc-add-leading-zeros5


arrow blue right bubble 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 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 the Kutools > Text Tools > Add Text.

doc-add-leading-zeros6

Step 4: In the Add Text dialog box, enter zeros in the Text box, and select the Before first character option.

doc-add-leading-zeros7

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

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

Comments  

+6#myq2013-10-17 07:56
WHAT 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
Reply | Reply with quote | Quote
+3#S2013-12-20 17:53
Use the =Text("000",A1) formula above. It will do that, I just tried it with great success.
Reply | Reply with quote | Quote
+2#Ali2013-11-20 08:42
Thanks, Its very helpful for me.
Reply | Reply with quote | Quote
+1#Surbhit2013-11-28 08:03
thanks....awsum tools
Reply | Reply with quote | Quote
+2#Pam2013-12-14 17:34
Ok so what if your field has A1234 and you want to add a 0 in front of the A how do you do that?
Reply | Reply with quote | Quote
+1#peter2014-01-23 10:41
I have used concatenate to create two leading zeros in number not text. A1 00 B1 00 C1 1234 = 001234
my problem is the worksheet needs to be uploaded into a program and the C1 cell is a problem because it has the concatenate formula and is not recognised as a number. Can you help?
Reply | Reply with quote | Quote
+1#Blake2014-01-30 20:39
Simple fix. After using the aforementinoed formula, copy all cells that contain formulas and right click and 'paste values'. This will keep the values on the spreadsheet (formula results) and get rid of the formulas for you.
Reply | Reply with quote | Quote
0#peter2014-02-13 09:53
Thanks, I did try that however when pasted as values the result goes to text and not a number. Any ideas appreciated
Reply | Reply with quote | Quote
+1#Patrick2014-02-11 18:33
Excellent fix. Thanks
Reply | Reply with quote | Quote
0#Daniella Kane2014-02-12 01:49
Thia was AWESOME!!! Thanks!!!!
Reply | Reply with quote | Quote
+3#Pam2014-02-21 16:43
Still not working - I have a alphanumeric number A1234 that I need to put a zero in front of the A how do you do that?
Reply | Reply with quote | Quote
-1#Grant2014-02-28 09:27
how about adding a sequence to an existing text string, where a new sequence is started when the text string changes. for example,
Reply | Reply with quote | Quote
0#Blake2014-02-28 22:45
Could you give another example? I am unable to see the one you posted.
Reply | Reply with quote | Quote
0#Subhendu Baliarsingh2014-03-11 16:38
Thanks....its very very helped me
Reply | Reply with quote | Quote
0#cmd2014-03-14 19:31
First off this has been a very helpful site. Thanks!

One tip for those like me who are trying to add leading zeros & leading text, try using the "&" to pull everything together.

Example:
="Bob"&(Text(value,"000000")). If the value (number) is 123, the result will be... Bob000123
Reply | Reply with quote | Quote
0#C2014-04-03 13:37
Instructions on how to add leading zeros.
Reply | Reply with quote | Quote
0#Jen2014-04-07 18:56
What if there are dashes, for example 3-14-50-12, 10-12-48-6. But you only need there to be a 0 in front of the single digit at the beginning?
Reply | Reply with quote | Quote
0#wani2014-05-14 09:39
cmd, you are very helpful. thanks!!
Reply | Reply with quote | Quote
0#Hafiz Mohammad Yasee2014-05-14 11:48
thanks I found my problem from your site.
Reply | Reply with quote | Quote
+1#ANSELM2014-05-26 18:23
THIS IS JUST AWESOME. MANY THANKS.
PLEASE I STILL HAVE ONE CHALLENGE ON THIS, I HAVE A RANGE TO WORK ON, FIRST I HAVE TO ROUND THE NUMBERS UP TO 10, BUT I HAVE THEM IN 3 DIFFERENT DIGITS (6, 7 & 8) SCATTERED ALL OVER THE RANGE. MY PROBLEM IS HOW TO INPUT A FOMULAR THAT WILL ENABLE THE EXCEL DETECT HOW MANY ZEROS EACH OF THE NUMBERS WILL HAVE IN FRONT TO ROUND UP TO 10.
Reply | Reply with quote | Quote
0#Firat2014-06-08 15:03
Great tips! Text function just saved my day, thank you for this simple solution.
Reply | Reply with quote | Quote
0#Marlene2014-07-23 10:47
Thanks a lot! It saved me !
Reply | Reply with quote | Quote
0#seema sahu2014-08-06 07:46
thnx my prob is solved.
Reply | Reply with quote | Quote

Add comment


Security code
Refresh