Note: The other languages of the website are Google-translated. Back to English

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. 

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!


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
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!
officetab bottom
Comments (72)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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
myq
This comment was minimized by the moderator on the site
Use the =Text("000",A1) formula above. It will do that, I just tried it with great success.
S
This comment was minimized by the moderator on the site
If statements can cover multiple situations if that is needed
Dylan
This comment was minimized by the moderator on the site
all you need to do is ' before the cero and than whatever number you want
Jorge
This comment was minimized by the moderator on the site
Thank you Jorge, that's the simplest and quickest solution to add zeros in front of numbers .
Joey
This comment was minimized by the moderator on the site
I too found this to be the easiest way! Simply used the "Find & Replace" to edit the entire document. Thank you!
Sam
This comment was minimized by the moderator on the site
Thanks, Its very helpful for me.
Ali
This comment was minimized by the moderator on the site
thanks....awsum tools
Surbhit
This comment was minimized by the moderator on the site
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?
Pam
This comment was minimized by the moderator on the site
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?
peter
This comment was minimized by the moderator on the site
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.
Blake
This comment was minimized by the moderator on the site
Thanks, I did try that however when pasted as values the result goes to text and not a number. Any ideas appreciated
peter
This comment was minimized by the moderator on the site
Excellent fix. Thanks
Patrick
This comment was minimized by the moderator on the site
Thia was AWESOME!!! Thanks!!!!
Daniella Kane
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?
Pam
This comment was minimized by the moderator on the site
Thanks....its very very helped me
Subhendu Baliarsingh
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
cmd
This comment was minimized by the moderator on the site
Thanks a lot for the help given using Excel.
Ms. R Kumar
This comment was minimized by the moderator on the site
Thanks for this tip CMD, this is exactly what I was trying to do!!
Melissa
This comment was minimized by the moderator on the site
Instructions on how to add leading zeros.
C
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?
Jen
This comment was minimized by the moderator on the site
cmd, you are very helpful. thanks!!
wani
This comment was minimized by the moderator on the site
thanks I found my problem from your site.
Hafiz Mohammad Yasee
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.
ANSELM
This comment was minimized by the moderator on the site
Great tips! Text function just saved my day, thank you for this simple solution.
Firat
This comment was minimized by the moderator on the site
Thanks a lot! It saved me !
Marlene
This comment was minimized by the moderator on the site
thnx my prob is solved.
seema sahu
This comment was minimized by the moderator on the site
very good topic, i have got my work done.
mutale kay
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.
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"
cmd
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations