Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Here is a quick tip to add awesome to your Wednesday.

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what.

Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.

See this short demo to understand how to get this work.

Bonus Tip – Use fixed number of zeros

For example, if you want the number to show up in 5 digits (with leading 0s if needed), you can use the cell format code 00000.

• Just select the cells and press CTRL+1
• From Number tab choose “Custom”
• Enter the format code as 00000
• Done!

Aside, you can see how this formatting works.

That is all for now. Have a great evening then 🙂

More on Cell Formatting

Excel allows you to format cells in myriad ways, some of which may baffle you. But Chandoo.org got your back! We have written several articles to help you master the cell formatting. Read on,

PS: We had a minor hiccup with our newsletter. Many of you did not get it for last 7 days. It is fixed now. So you might get one big email from Chandoo.org with all the missed posts.

Share this tip with your friends

Written by Chandoo

30 Responses to “Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]”

You could always use a leading ' also, of course.

It's probably best to use the custom number format than formatting as text out using the leading apostrophe. The only exception that comes to mind is if the number is a credit card number. Excel lewis only the first 15 digits, and turns the rest to zeros.

It doesn't really work, I'm sorry.

After setting the "00000" in custom. The number does change to "00023". However, if you go back to cell format, it will show up under "Special" -> "Zip Code". i'm using excel 2007.

Also, I have customer ID that starts with zero from another system. When that ID (along with a table of data) is pulled into Excel it will show as 000023 with a cell format "General". if you F2 that cell the zeros would be gone so I don't want to mess with it.

The problem is that you can't do vlookup between 2 cells one with a zip code format and the other one as General. I have seen that in the past and it won't work.

I ended up getting a different solution, not smart/efficient but works for me. I enter "000"&23, copy and paste as value on the same cell. This way, the ID 00023 generated by a different system dl to Excel is compatible to other data. To make my life easier I have generated, on the side, a list of customers whose ID start with 0. When I need them, I'll just pull them to the worksheet where I need them.

The problem is not a lookup in different formats, it's that some of your values are numerical and some text.

Your values that visibly change when you use the 00000 format are numbers. The values that come in from another system with leading zeros, come in as text which preserves the zeros as text. The General format shows the text complete with leading zeros and numbers without any leading zeros and without trailing zeros after the decimal point. F2 makes Excel re-evaluate the numbers, and converts them from text with leading zeros to numbers without.

If it were my data, I'd convert everything to numbers and apply appropriate number formats. Then I wouldn't have to keep a separate list of special companies, and change my data whenever one of them were involved.

It seems like a really bad idea to suggest using text to preserve leading zeros, particularly in light of all of the concern these days abut spreadsheet integrity and managing risks in spreadsheets.

Very good point. When you are entering numbers, I see no reason why you would want leading zeros. But when you are entering text data (such as customer numbers, invoice numbers, product codes) that looks like numbers, then using TEXT format is actually a better choice since you are not going to sum-up or do other such arithmetic with them.

If a field contains entries that may be numbers or text, then treat the field as text. If the field only contains numbers, I still think you should retain their numerical values and adjust their appearance using number formats.

Beware if doing arithmetic operations to a "Text Number" - =/+ an individual cell will return something meaningfull, SUM() will ignore a non-numeric style and return 0. Prefacing with a ' will also generate the same behaviour.

Chandoo, sometimes these numbers are expected in a fixed format.

In where I work, the employee id is always in 7 digits. So in columns that indicate employee id, we always format with Custom 7 digits. this really helps easy reading.

I always use the Custom format rather than the text or using ' before the numbers because the latter two cases will not help using any formulas.

Murugaraj, even if the employee ID is stored in a company database as an integer, you're never going to be treating the ID as a number (such as performing arithmetic on it.) A system I work with has a 10 digit ID that is all numbers, but starts with zeroes in some cases. We always treat these IDs as strings.

One problem I have is that even though they're strings, exporting CSV from the database results in columns that are treated as numbers and lose the starting zeroes. In this case, I usually add another column with the formula =REPT(0,8-LEN(A1))&A1. This formula will create a column with a string padded with the correct number of zeroes (enough for 8 digits in this example.)

I frequently need to change a list mixed with 3, 4, and 5 digits to uniformly 5 digits by adding leading zeros.

I was excited about the custom format solution, but then I realized the underlying cell value is still 3 or 4 digits, realizing that custom formatting is just that, merely a format applied to a value. If I copy and paste to another location, the 3 or 4 digit values remain.

The other way to write a formula such as ="0"&A1 but I like your method better. Any workaround to make it work in my case?

which shows a 5-digit number in the cell but treats it as text. Copy and paste deals with the 5-digit text, not the underlying numerical value.

This works for CSV export - Like!

I am a newbie to excel and never got an opportunity to explore much on it. As I started working on it, the results were startling. I am really impressed by the way the site is organized and I am book marking this site as a one stop to my excel problems.

How to format 28,654,124 to show as 28,000,000 using custom formatting (without using round() formula)

@Nelson: If the numbers are always in millions, you can try below custom format code:

If he really wants 28.6 million to appear as 28.0 million instead of 29.0 million, he'll have to use a formula

Thank You Peltier.

My focus was on getting six zeroes. I overlooked.

Thank You Chandoo.

The output was 29000,000

How to VLOOKUP serial IDs with leading zeros & without zeros

Item Sales Qty Month

@Rebecca. strange, but you are right. SUMIF(s) would forcefully convert condition values to numbers. You can use SUMPRODUCT in cases like this.

I have tried all your suggestions above. still can't get leading zeros back into zip code field that were dropped when I imported a csv file. I saw in the custom format where I could add "0" back in but only to five digits -- my zip code field has all 9 digits (zip plus4) all together

Example1: 9digit Zip code field for Puerto Rico After import: 6105458

should be: 006055132

should be 019601714

If you have your ZIP-code in cell A1, you could try the following formula in cell B1 =IF(LEN(A1)<9;REPT("0";9-LEN(A1))&A1;A1)

I know this is a poor work-around 🙁

God bless you real good, you just saved someone's ass in ma office !

I was trying to add zero in my excel but it’s not happening. I was quite frustrated and i start searching the way to do it and luckily i got this page. I have did according to your described way and it’s working. Thanks for sharing such a valuable resource.

I have this column and I am used this formule

I need include the zero , but i have only 2, 5, 6, 10.

Can you help me? Please.

what format should I use if the cell contains a formula?

If I use text it doesn't resolve the formula but displays it instead.

I need the last two digit of the decimal, Eg: 1425.50 or 1425.00 then I have to get 50 or 00. I tried this this formula but zero is not getting.=IF(IFERROR(FIND(".",B129:B129),0)>0,MID(B129:B129,FIND(".",B129:B129)+1,99),"")

i have a list of phone nos i copied from word to excell all starting with zero but the leading zero doesn't appear . what do i do