How to prevent automatic truncation of leading zeros in Excel cell

Question

If I paste 04562, it automatically truncates the leading zero and stores it as 4562.

I want it to to store it as 04562.

Spermatophore

Answer 1

If you are using MS Excel to edit the cells where you need to store the numbers with leading zero, just format and define the cell content as TEXT. If you are storing it programmatically, you can put the value in a quote '' for the same.

Update

I just noticed that if I go to "Format Cells" and select custom format and put, say 0000 to the format, any number that I put in the cell, it is is less than 4 digits, it still maintain the 4 digit length with the leading '0', like 0001, 0099, 0120, 9999

Czar

How to Set the format into text. Plz help im a beginner in excel – Spermatophore

You can also format as a zip code in the custom section of the number format dialogue. – Autolysin

it doesnt work if you have very long numbers like 1,33444E15 , if you do that then you would have the text "1,33444E15 " – Cosine

Even if your column is set to TEXT, a find-and-replace that changes data such that it looks like a number will still trigger Excel to remove leading zeroes, e.g. replacing B with an empty string in 01B will change it to 1, not 01. Excel is many kinds of awful, be careful.. – Supranational

Answer 2

While all of this is accurate workarounds for converting a number to text so that the leading zeros will be preserved, it is not an answer to the question.

The answer to your question is that if you want a number with leading zeros in Excel, you cannot have it - at least during the import.

The Microsoft developers, for whatever reason built Excel to read in 03 as a number and truncate the leading zero, resulting in 3. There is no way to prevent this.

It is unfortunate because all workarounds assume you know what columns have values that might have leading zeros so that you can take steps * after the fact * to try and fix your data.

By the way, Google Sheets does the same thing.

So the short answer is that there is no spreadsheet application (at least that I am aware of) where you can read in a csv file that might have oh, say 00453 as a number and have it imported as 00453 and not 453.

Vermin

You talk about importing. Concerning importing from a csv file, I believe it is possible to retain zeros. In one of importing steps you can choose column data format. If you choose "text", the leading zeros will remain. – Camarilla

Also, the question doesn't ask about importing from a CSV. So your answer really isn't an answer to the question that is being asked here. – Julesjuley

Answer 3

If you just double click on a csv text file to open it, Office 2010 will open it and truncate any leading zeroes because it is general formatting every column by default. Instead, you can IMPORT the file: Click on the "Data" tab - the "Get External Data" options include "From Text" - that opens a wizard where you can tag the file to import, preview it, and define the format for any column containing lead zeroes as "text." When you import this way, the leading zeroes are preserved.

Highroad

This is the best answer. When you import it you need to click on the columns you want to set to text. Wow such a frustrating experience this can be! – Proofread

Answer 4

Click the cell and On the Home tab, in the Number group, click the dialog box launcher Button image next to Number.

In the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.

https://support.office.com/en-us/article/Keep-leading-zeros-in-number-codes-1bf7b935-36e1-4985-842f-5dfa51f85fe7

Jaredjarek

Answer 5

You can save it using the following format.

In excel, you can

1) set the format to "Text" by selecting the proper cell, right clicking, selecting "Format Cell". Then under the "Number" tab, select "text". Anytime you enter a number, it will be read as text

OR

2) precede any number with an apostrophe (') and Excel will accept it "as is"

If you are using VBA, you can do the following

1) Select the cell you need to format and change the number format option

Range("A1").NumberFormat = "@"

When you read the data (from a cell in Excel or from VBA), it will contain any leading zeros that were entered.

Rhaetian

Answer 6

The problem is actual stored values vs. displayed values... ALL of these presentation "fixes" do not address the loss of leading zeros in the stored value.

So if you are entering a value into a cell or importing the end result is the same, you cannot preserve the leading zeros in the stored value REGARDLESS of formatting constraints on the cell.

Blatherskite

Answer 7

You can also just prefix your value with a ', though this flags the cell until you make it a text field.

Tanga

That will work when entering the text manually. But when pasting, using Kangkan's solution will work. – Autolysin

Answer 8

I thought it might be helpful to do a step by step for some people that might need a little more help finding where to select these things.

1. Select the square or squares you need by clicking on them/highlighting them.

2. Stay on the Home tab up at the top. All the way on the right side, on the second box to the right, there is an option that says Format. Click on that.

3. Click on Format Cells... all the way at the bottom. The first tab should say Number. On that Number tab, there's an option that says Text. Click on it. Now, press OK at the bottom, and done.

Rabassa

Answer 9

If the amount you are going to input will have a fixed amount of digits, let's say for example, zip code which is 5 digits long, then you can apply a customized format to this cell with the mask: 00000. With this, no matter what amount, which does not exceed 5 digits long, you input, the leading zeroes will be kept.

1. Right-clic on the cell to format.
2. Clic on Cells Format.
3. Choose Number tab.
4. Clic on Customize option.
5. Clic on any the options listed on the listbox on the right.
6. Clic on Type texbox, and overwrite its content with "00000".
7. Clic on Ok.

Ku

Answer 10

Here was my best and easiest solution. Because Excel will change the numbers if you already have the data typed in. Only works if you type in after changing the format.

1. Add a new column
2. Highlight the column and change format to TEXT via right clicking and format cells
3. Take your zip codes or numbers clumn and copy it
4. Paste into Notepad (to get rid of formatting)
5. Copy the notepad data and paste into your new Excel column

Thiazole

Answer 11

I don't know if this is perfect work around but, I tried it the first time and it worked.

On the left cell you don't see leading 0 and I wanted to add leading 0. The fix is to use the formula ="0"&A3(Cell number)

(remember to set your and it should work perfectly see example below.

Leathery

Answer 12

If you are importing / opening a .csv file that has leading zeroes, use Open Office or Libre Office. When you open the file, it will present a dialog to specify things like the column delimiter. You can also select one of the columns and specify the type as "text" which will preserve the leading zeroes.

If you then save it as a .xlsx file, it can be opened with Excel and will preserve the leading 0.

It seems like there should be a way to specify this on import to Excel but I haven't been able to find it.

Sabol

Answer 13

Adding an apostrophe before the number that starts with a zero will keep the full number but not post the apostrophe. For example: if your number is 08753 excel will truncate the number to 8753 unless you type '08753. Excel will then fill the cell with 08753. Easy!

Pavis

Answer 14

If you simply paste your clipboard, then leading and trailing 0s get taken out. But, instead, if you select your entire worksheet (Ctrl+A) and then set the format to "Text":

And then paste your clipboard, the contents will keep the leading and trailing 0s.

The one downside to this is that excel will report that each cell has an error and there doesnt seem to be an easy way to "ignore all errors":

Pemmican

Answer 15

Many have stated above that this is not an easy maneuver. Here is a suggestion.

Say your erroneous zip codes (no leading 0's) are in column A.

1. Insert a dummy column, let's use B.
2. In column B put formula, =A1*1 and copy down to capture all zips. After you do this you should see leading zeros, however you are not finished. They will still not be read.
3. Copy column B, then paste the contents of column B to a new workbook.
4. Save the new workbook as a .csv file (when saving click on the "Save as Type" box and select CSV comma delimited).
5. Open that new CSV file you just created.
6. Copy, then paste the contents directly over your crappy incorrectly formatted zip codes. (this means you can't have your paste location filtered, keep that in mind). The column contents must match or your data will be mismatched.
7. Click and select all the zip codes you just pasted.
8. Select format cells and choose: Special, then Zip Code
9. Hopefully your zips will hold to 5 digit formatting and should be able to be read in formulas and such.

It's a long fix and has worked for me. Hope it works for you!

Conqueror