Skip to main content

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.

doc add leading zeros 1

Method 1: Add or pad the leading zeros to numbers to equal certain length with Text function

Method 2: Add or pad the leading zeros to numbers to equal certain length with Format Cells function

Method 3: Add or insert the same number of leading zeros to numbers with Concatenate function

Method 4: Add or insert the same number of leading zeros to numbers and text with Kutools for Excel

Method 1: Add or pad the leading zeros to numbers to equal certain length with Text function

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:

doc add leading zeros 2

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:

doc add leading zeros 3

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:

doc add leading zeros 4

Method 3: Add or insert the same number of leading zeros to numbers 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",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:

doc add leading zeros 5

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. 
Go to Download
Free Trial 30 daysPurchase
PayPal / MyCommerce

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:

doc add leading zeros 7

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.

Add leading zeros to numbers or text with Kutools for Excel

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

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (76)
Rated 5 out of 5 · 1 ratings
Алексей
Алексей
This comment was minimized by the moderator on the site
Благодарю за материал 🙏
Reply
0
0
Rated 5 out of 5
Chelas
Chelas
This comment was minimized by the moderator on the site
Hola me pueden ayudar tengo una lista de datos cada dato contiene letras y números pero requiero ampliar su longitud a 12 con los ceros en la parte de enmedio conocen una formula que me ayude. Ejemplo: dato = DID45 expectativa = DID000000045
Reply
0
0
Amanda Lee
Amanda Lee     Chelas
This comment was minimized by the moderator on the site
Hi Chelas,

Are there always three letters before two number characters?
If so, you can use the Add Text feature, and choose Specify option. And enter 3 in the input box to add 7 zeros after the 3rd character.https://www.extendoffice.com/images/stories/comments/ljy-picture/add-seven-zeros.png

Amanda
Reply
Report
0
0
TRAVOZ STEVEN
TRAVOZ STEVEN
This comment was minimized by the moderator on the site
I got my already programmed and blank ATM card to withdraw the maximum of $100,000 MONTHLY for a maximum of 12 MONTHS. I am so happy about this because i got mine last week and I have used it to get $400,000 already. () is giving out the card just to help the poor and needy though it is illegal but it is something nice and he is not like other scam pretending to have the blank ATM cards. And no one gets caught when using the card. Get yours from THOMAS PERRY Hackerz today! Just send an email to ()
Reply
0
0
superstar
superstar
This comment was minimized by the moderator on the site
В примере ошибка, нужно ставить не запятую, а точку с запятой: "= Текст (A2, "00000")"
Reply
0
0
Narcisse
Narcisse
This comment was minimized by the moderator on the site
thank you, very helpful
Reply
0
0
Bernice
Bernice
This comment was minimized by the moderator on the site
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
Reply
0
0
Rashida
Rashida
This comment was minimized by the moderator on the site
Thank you for sharing..it's really use for me.
Reply
0
0
Shirley
Shirley
This comment was minimized by the moderator on the site
Thank you for helping me out. You saved my day!
Reply
0
0
Peter
Peter
This comment was minimized by the moderator on the site
Worked well for me. Thanks alot.
Reply
0
0
Ryan
Ryan
This comment was minimized by the moderator on the site
Hey, 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" Thanks
Reply
0
0
MANMOHAN
MANMOHAN
This comment was minimized by the moderator on the site
Use 0& example: =0&0&A1 (A1 is cell in which 00 is to be added as prefix)
Reply
0
0
Andrea
Andrea
This comment was minimized by the moderator on the site
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?
Reply
0
0
jyoti
jyoti     Andrea
This comment was minimized by the moderator on the site
you can use "REP FUNCTION AND LENGTH FUNCTION TOGETHER " for such problems.

Example : =REP(0,10-LEN(C4))&C4
Reply
0
0
Blake
Blake     Andrea
This comment was minimized by the moderator on the site
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.
Reply
0
0
Gaz
Gaz
This comment was minimized by the moderator on the site
CMD & BLAKE, You are absolute whizzkids! :-) thanks
Reply
0
0
Duane
Duane
This comment was minimized by the moderator on the site
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.
Reply
0
0
Sharon
Sharon
This comment was minimized by the moderator on the site
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/ideas? Thanks!
Reply
0
0
Peter
Peter     Sharon
This comment was minimized by the moderator on the site
A1=5 B1 = A1/10000 = .0005
Reply
0
0
Blake
Blake     Sharon
This comment was minimized by the moderator on the site
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?
Reply
0
0
scott.george@siemens
scott.george@siemens
This comment was minimized by the moderator on the site
Adding leading zeros
Reply
0
0
Anup
Anup
This comment was minimized by the moderator on the site
Hello I want to add "00" for A1 column but every cell contain random numeric digits. Thanks in advance.
Reply
0
0
Hayley
Hayley
This comment was minimized by the moderator on the site
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, 00633597038029E6. 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 00633597038029000000. However, it also only does this when it drops the 2 zeros from the front. Leaving it as 633597038029000000. 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?
Reply
0
0
Johncy
Johncy
This comment was minimized by the moderator on the site
How to convert a number into thousands or lakhs, depending on the number of zeroes required. Is it possible through formatting.
Reply
0
0
Gin
Gin
This comment was minimized by the moderator on the site
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!
Reply
0
0
Twowolves
Twowolves
This comment was minimized by the moderator on the site
Nice! Just ran into this problem at work and was amazed at how easy the solution was! Thanks!
Reply
0
0
kate
kate
This comment was minimized by the moderator on the site
thanks for the info !
Reply
0
0
Zella
Zella
This comment was minimized by the moderator on the site
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!
Reply
0
0
Smithc530
Smithc530
This comment was minimized by the moderator on the site
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. efbdcekddgfegdfe
Reply
0
0
oc
oc
This comment was minimized by the moderator on the site
Thank you Blake...works perfec.
Reply
0
0
Blake
Blake
This comment was minimized by the moderator on the site
#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.InputBox( _ 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.Count > 1 Then MsgBox "Please select only one column at a time.", vbExclamation Exit Sub End If Set sht = rng.Parent Application.ScreenUpdating = False If hdr = vbYes Then Set col = sht.Range(sht.Cells(2, rng.Column), _ sht.Cells(sht.Rows.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.Cells(1, rng.Column), _ sht.Cells(sht.Rows.Count, rng.Column).End(xlUp)) For Each c In col.Cells c.Value = Replace(c.Value, ".", "") Next c End If Application.ScreenUpdating = True End Sub If you have any problems, let me know.
Reply
0
0
oc
oc     Blake
This comment was minimized by the moderator on the site
Thank you! Works like a charm..
Reply
0
0
oc
oc
This comment was minimized by the moderator on the site
HOW CAN I HAVE A VALUE OF 0.0010 SHOW AS 00010 AND A VALUE OF 1.001 AS 1001? IN EXCEL? Thanks.
Reply
0
0
HARI
HARI
This comment was minimized by the moderator on the site
thx a lot. I like to learn a lot
Reply
0
0
Tanvir
Tanvir
This comment was minimized by the moderator on the site
tanx boss, really so nice
Reply
0
0
Afra
Afra
This comment was minimized by the moderator on the site
how to 8.00 ?? please ans
Reply
0
0
rfruae
rfruae     Afra
This comment was minimized by the moderator on the site
Use the Comma function (Accounting)
Reply
0
0
Brandon
Brandon
This comment was minimized by the moderator on the site
Thanks for sharing the information, example well formatted, baie dankie :)
Reply
0
0
Lizz
Lizz
This comment was minimized by the moderator on the site
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")Result 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")
Reply
0
0
cmd
cmd     Lizz
This comment was minimized by the moderator on the site
Try Paste Special > Values and number formats
Reply
0
0
vartaxe
vartaxe
This comment was minimized by the moderator on the site
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 ? :/
Reply
0
0
cmd
cmd     vartaxe
This comment was minimized by the moderator on the site
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
Reply
0
0
Jamie
Jamie
This comment was minimized by the moderator on the site
Absolutely perfect, the =Text (A1, "00000") Formula works perfectly for me! Excel can do some incredible things, its just knowing these formulas!!
Reply
0
0
Ruby
Ruby
This comment was minimized by the moderator on the site
for you because you are the best
Reply
0
0
Delio
Delio
This comment was minimized by the moderator on the site
does not work i used the exact numbers and i receive formula error =text(B4,"00000") from A1 being 2932
Reply
0
0
Matt_m
Matt_m
This comment was minimized by the moderator on the site
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.
Reply
0
0
cmd
cmd     Matt_m
This comment was minimized by the moderator on the site
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"
Reply
0
0
mutale kay
mutale kay
This comment was minimized by the moderator on the site
very good topic, i have got my work done.
Reply
0
0
seema sahu
seema sahu
This comment was minimized by the moderator on the site
thnx my prob is solved.
Reply
0
0
Marlene
Marlene
This comment was minimized by the moderator on the site
Thanks a lot! It saved me !
Reply
0
0
Firat
Firat
This comment was minimized by the moderator on the site
Great tips! Text function just saved my day, thank you for this simple solution.
Reply
0
0
ANSELM
ANSELM
This comment was minimized by the moderator on the site
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
0
0
Hafiz Mohammad Yasee
Hafiz Mohammad Yasee
This comment was minimized by the moderator on the site
thanks I found my problem from your site.
Reply
0
0
wani
wani
This comment was minimized by the moderator on the site
cmd, you are very helpful. thanks!!
Reply
0
0
Jen
Jen
This comment was minimized by the moderator on the site
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
0
0
C
C
This comment was minimized by the moderator on the site
Instructions on how to add leading zeros.
Reply
0
0
cmd
cmd
This comment was minimized by the moderator on the site
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
0
0
Melissa
Melissa     cmd
This comment was minimized by the moderator on the site
Thanks for this tip CMD, this is exactly what I was trying to do!!
Reply
0
0
Ms. R Kumar
Ms. R Kumar     cmd
This comment was minimized by the moderator on the site
Thanks a lot for the help given using Excel.
Reply
0
0
Subhendu Baliarsingh
Subhendu Baliarsingh
This comment was minimized by the moderator on the site
Thanks....its very very helped me
Reply
0
0
Pam
Pam
This comment was minimized by the moderator on the site
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
0
0
Daniella Kane
Daniella Kane
This comment was minimized by the moderator on the site
Thia was AWESOME!!! Thanks!!!!
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations