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

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-add-leading-zeros1 -2 doc-add-leading-zeros2

 



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 zeros. 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 zeros 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 Kutools > Text > 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.

Free Download Kutools for Excel 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 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...

Screen shot of Kutools for Excel

btn read more btn download btn purchase

Comments  

Permalink +9 myq
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
2013-10-17 07:56 Reply Reply with quote Quote
Permalink +4 S
Use the =Text("000",A1) formula above. It will do that, I just tried it with great success.
2013-12-20 17:53 Reply Reply with quote Quote
Permalink -2 Dylan
If statements can cover multiple situations if that is needed
2015-06-30 19:03 Reply Reply with quote Quote
Permalink +1 Jorge
all you need to do is ' before the cero and than whatever number you want
2016-03-31 20:25 Reply Reply with quote Quote
Permalink 0 Joey
Thank you Jorge, that's the simplest and quickest solution to add zeros in front of numbers .
2016-09-02 18:17 Reply Reply with quote Quote
Permalink +1 Ali
Thanks, Its very helpful for me.
2013-11-20 08:42 Reply Reply with quote Quote
Permalink -1 Surbhit
thanks....awsum tools
2013-11-28 08:03 Reply Reply with quote Quote
Permalink +5 Pam
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?
2013-12-14 17:34 Reply Reply with quote Quote
Permalink -1 peter
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?
2014-01-23 10:41 Reply Reply with quote Quote
Permalink +1 Blake
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.
2014-01-30 20:39 Reply Reply with quote Quote
Permalink -4 peter
Thanks, I did try that however when pasted as values the result goes to text and not a number. Any ideas appreciated
2014-02-13 09:53 Reply Reply with quote Quote
Permalink 0 Patrick
Excellent fix. Thanks
2014-02-11 18:33 Reply Reply with quote Quote
Permalink -1 Daniella Kane
Thia was AWESOME!!! Thanks!!!!
2014-02-12 01:49 Reply Reply with quote Quote
Permalink +4 Pam
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?
2014-02-21 16:43 Reply Reply with quote Quote
Permalink -3 Grant
how about adding a sequence to an existing text string, where a new sequence is started when the text string changes. for example,
2014-02-28 09:27 Reply Reply with quote Quote
Permalink +1 Blake
Could you give another example? I am unable to see the one you posted.
2014-02-28 22:45 Reply Reply with quote Quote
Permalink 0 Subhendu Baliarsingh
Thanks....its very very helped me
2014-03-11 16:38 Reply Reply with quote Quote
Permalink +5 cmd
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
2014-03-14 19:31 Reply Reply with quote Quote
Permalink -1 Ms. R Kumar
Thanks a lot for the help given using Excel.
2014-09-01 08:16 Reply Reply with quote Quote
Permalink +1 Melissa
Thanks for this tip CMD, this is exactly what I was trying to do!!
2014-12-04 20:47 Reply Reply with quote Quote
Permalink +1 C
Instructions on how to add leading zeros.
2014-04-03 13:37 Reply Reply with quote Quote
Permalink +1 Jen
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?
2014-04-07 18:56 Reply Reply with quote Quote
Permalink +1 wani
cmd, you are very helpful. thanks!!
2014-05-14 09:39 Reply Reply with quote Quote
Permalink -1 Hafiz Mohammad Yasee
thanks I found my problem from your site.
2014-05-14 11:48 Reply Reply with quote Quote
Permalink +1 ANSELM
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.
2014-05-26 18:23 Reply Reply with quote Quote
Permalink -1 Firat
Great tips! Text function just saved my day, thank you for this simple solution.
2014-06-08 15:03 Reply Reply with quote Quote
Permalink -1 Marlene
Thanks a lot! It saved me !
2014-07-23 10:47 Reply Reply with quote Quote
Permalink -1 seema sahu
thnx my prob is solved.
2014-08-06 07:46 Reply Reply with quote Quote
Permalink +1 mutale kay
very good topic, i have got my work done.
2014-09-09 08:05 Reply Reply with quote Quote
Permalink +1 Matt_m
Is there anyway to ad quotation marks before and after any text entered into a cell? It looks like Kutools could do it but is there anyway to do it with just Excel? Thanks in advance.
2014-09-09 16:30 Reply Reply with quote Quote
Permalink +1 cmd
Here is a quick way to add the quotation mark before & after your text...

1. Enter " into a blank cell.
2. Formula... =(select cell with the ")&(select cell with your text)&(select cell with the ")
Example: " in cell A1 and Barry in cell C1... =A1&C1&A1.
Result: "Barry"
2014-09-10 20:44 Reply Reply with quote Quote
Permalink +1 Delio
does not work i used the exact numbers and i receive formula error

=text(B4,"00000") from A1 being 2932
2014-09-22 20:45 Reply Reply with quote Quote
Permalink +1 Ruby
for you because you are the best
2014-10-17 15:11 Reply Reply with quote Quote
Permalink -1 Jamie
Absolutely perfect, the =Text (A1, "00000") Formula works perfectly for me! Excel can do some incredible things, its just knowing these formulas!!
2015-02-05 13:51 Reply Reply with quote Quote
Permalink -1 vartaxe
how to do when cells look like this:

ABW-M-1
BAS-B-74
ESU-F-3

and need them to be:

ABW-M-001
BAS-B-074
ESU-F-003

? :/
2015-04-13 12:30 Reply Reply with quote Quote
Permalink +1 cmd
1) Use Text to Columns (Data > Text to Columns) to split out the last number. - Use fixed width and split after the second hyphen/dash.
2) Use =Text(B1,"000") formula for the numbers you need changed to 3 digits.
3) Use Concatenate formula to recombine the data back into a single string.


1. Split with Text to Column
2. Insert formula =Text(B1,"000") (Column C)
3. Concatenate Column A & Column C (Column D)

A B C D
ABW-M- 1 001 ABW-M-001
BAS-B- 74 074 BAS-B-074
ESU-F- 3 003 ESU-F-003
2015-04-14 14:59 Reply Reply with quote Quote
Permalink -2 Lizz
Thank you for all your helpful tips. I was wondering if there is a way to format the answers from a formula to be able to copy the result into another program as a number.
Ex. I need to use the number produced from =Text (A1, "000")
Cell A1 12345
Cell A2 =Text (A1,"000000")Re sult being 012345
Copy & Paste 012345 as a number into another program without the formula being pasted into the other program.
When I copy & paste 012345 the result is =Text (A1, "000000")
2015-04-21 15:32 Reply Reply with quote Quote
Permalink +2 cmd
Try Paste Special > Values and number formats
2015-05-11 14:12 Reply Reply with quote Quote
Permalink -1 Brandon
Thanks for sharing the information, example well formatted, baie dankie :)
2015-04-23 20:30 Reply Reply with quote Quote
Permalink -1 Afra
how to 8.00 ?? please ans
2015-05-06 10:51 Reply Reply with quote Quote
Permalink +1 rfruae
Use the Comma function (Accounting)
2015-07-21 10:55 Reply Reply with quote Quote
Permalink +1 Tanvir
tanx boss, really so nice
2015-10-11 10:30 Reply Reply with quote Quote
Permalink 0 HARI
thx a lot. I like to learn a lot
2016-02-02 02:59 Reply Reply with quote Quote
Permalink +1 oc
HOW CAN I HAVE A VALUE OF 0.0010 SHOW AS 00010 AND A VALUE OF 1.001 AS 1001? IN EXCEL? Thanks.
2016-02-11 17:23 Reply Reply with quote Quote
Permalink +3 Blake
#OC, I wrote you a VBA script. To execute, open your subject Workbook, ensure that the affected column is formatted as Text, then press alt+F11, then in the toolbar select Insert > Module. When the editor loads, paste the following code in and then execute:

Sub Column_Fix()

Dim rng As Range, col As Range, arr
Dim sht As Worksheet, c As Range, tmp

On Error Resume Next 'in case of cancellation
Set rng = Application.Inp utBox( _
Prompt:="Please select the column that contains the incorrect values." & _
" (e.g. Column A or Column B)", _
Title:="Select Range", Type:=8)

On Error GoTo 0


hdr = MsgBox("Does your selection contain a header?", vbYesNo + vbQuestion, "Header Option")

If rng Is Nothing Then Exit Sub

If rng.Columns.Cou nt > 1 Then
MsgBox "Please select only one column at a time.", vbExclamation
Exit Sub
End If

Set sht = rng.Parent


Application.Scr eenUpdating = False
If hdr = vbYes Then
Set col = sht.Range(sht.C ells(2, rng.Column), _
sht.Cells(sht.R ows.Count, rng.Column).End (xlUp))

For Each c In col.Cells
c.Value = Replace(c.Value , ".", "")
Next c
End If
If hdr = vbNo Then
Set col = sht.Range(sht.C ells(1, rng.Column), _
sht.Cells(sht.R ows.Count, rng.Column).End (xlUp))

For Each c In col.Cells
c.Value = Replace(c.Value , ".", "")
Next c
End If
Application.Scr eenUpdating = True
End Sub



If you have any problems, let me know.
2016-02-16 08:39 Reply Reply with quote Quote
Permalink +1 oc
Thank you! Works like a charm..
2016-02-17 15:26 Reply Reply with quote Quote
Permalink +1 oc
Thank you Blake...works perfec.
2016-02-17 15:08 Reply Reply with quote Quote
Permalink 0 Smithc530
I am no longer sure the place you're getting your info, but great topic. I needs to spend a while studying much more or understanding more. Thanks for wonderful info I was on the lookout for this info for my mission. efbdcekddgfegdf e
2016-06-05 06:19 Reply Reply with quote Quote
Permalink 0 Zella
Great tips! I need to make this number 1-354-0-2 be in the following format: 01-0354-00-02. Can you help??
Thanks!
2016-07-11 14:30 Reply Reply with quote Quote
Permalink 0 kate
thanks for the info !
2016-07-12 11:23 Reply Reply with quote Quote
Permalink 0 Twowolves
Nice! Just ran into this problem at work and was amazed at how easy the solution was! Thanks!
2016-08-09 13:35 Reply Reply with quote Quote
Permalink 0 Gin
Hi all,

I have dates in the format yyymmdd, e.g. 2080201 stays for 1st of Feb. 2008 (2008.02.01). how can I add a zero after the first two digits?

like: yyymmdd to yyyymmdd (e.g. 2080201 to 20080201)

Thanks!
2016-08-30 12:22 Reply Reply with quote Quote
Permalink 0 Johncy
How to convert a number into thousands or lakhs, depending on the number of zeroes required.
Is it possible through formatting.
2016-09-04 14:01 Reply Reply with quote Quote
Permalink 0 Hayley
Hi, I have a problem, but not sure if it can be fixed in Excel:

I have to export a file to CSV (currently the only option), within that file there are numbers that are usually 16 characters long, the first 2 are zeros, and sometimes, the last two figures might be E followed by a number, for example, 00633597038029E 6. In the system they are being exported from, it is consistent and there isn't a problem, however as soon as the CSV file is opened in Excel, it converts the E6 and replaces it with 6 zeros, the above number becomes 006335970380290 00000. However, it also only does this when it drops the 2 zeros from the front. Leaving it as 633597038029000 000. When excel does not decide to drop the 2 zeros, it isn't a problem and it leaves the number as E6, it is only on the occasion where it has for some reason, dropped the 2 zeros.

The problem I have is that it seems that by the time I have opened the file, it is too late to do anything as excel has already converted the numbers.

Is there anything that can be done about this?
2016-09-12 12:46 Reply Reply with quote Quote
Permalink 0 Anup
Hello

I want to add "00" for A1 column but every cell contain random numeric digits.

Thanks in advance.
2016-09-15 16:48 Reply Reply with quote Quote
Permalink 0 scott.george@siemens
Adding leading zeros
2016-11-18 17:47 Reply Reply with quote Quote
Permalink 0 Sharon
Hi! I have no idea if this is possible or not, but my boss would like to enter whole numbers into cells, but then have them converted into decimals (for example, he want to enter a 5 in the cell, but then have it converted to .0005). Another example that he gave is he might enter 49 and would like the cell to be .0049. Any suggestions/ide as? Thanks!
2016-12-30 18:17 Reply Reply with quote Quote
Permalink +1 Blake
Hi. You could enter a formula that divides the cell by 10000. For example, if your entry cell is A1 and your cell for formatting is B1, you can type the following in B1:

=A1/10000

Then type something in A1 and check. Does that work or do you need a more precise solution?
2017-01-05 14:12 Reply Reply with quote Quote
Permalink 0 Peter
A1=5
B1 = A1/10000
= .0005
2017-01-05 22:11 Reply Reply with quote Quote
Permalink 0 Duane
I deal alot with serial numbers of my companies product that includes leading zeros, which I frequently store in Excel sheets by pasting them out of our SAP system. Usually I can just format the cells as text before pasting them, which keeps the zeros, but occasionally I have to add them back in. Before finding this article I've struggled with that, but this works perfectly, thanks for the tip.
2017-01-23 16:03 Reply Reply with quote Quote
Permalink 0 Gaz
CMD & BLAKE, You are absolute whizzkids! :-)
thanks
2017-02-15 17:34 Reply Reply with quote Quote
Permalink 0 Andrea
I 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?
2017-02-16 19:00 Reply Reply with quote Quote
Permalink 0 Blake
Hi Andrea. Assuming your original text is in cell A2, you can type =TEXT(A2,"0000000000")

That will ensure that there are enough leading zeros so that you have 10 characters in each string.
2017-02-17 18:26 Reply Reply with quote Quote
Permalink 0 MANMOHAN
Use 0&
example: =0&0&A1 (A1 is cell in which 00 is to be added as prefix)
2017-03-23 15:30 Reply Reply with quote Quote
Permalink 0 Ryan
Hey, i was wondering how to put text in-front of a number with 0 decimal places.
ie. it comes up as " Sp3.14525524634 5 " and i want it to show " Sp3"


Thanks
2017-03-26 00:13 Reply Reply with quote Quote
Permalink 0 Peter
Worked well for me. Thanks alot.
2017-05-19 09:16 Reply Reply with quote Quote

Add comment


Security code
Refresh