Keep Leading Zeros in CSV Files Opened in Excel

To keep leading zeros in CSV data that Excel opens, do not double-click the file. Import it through Data, From Text/CSV, and set each zero-padded column to Text before loading. The zeros were never missing from the CSV itself; Excel drops them while reading. Avoid saving over the original after a plain open.

Are the leading zeros still inside the CSV file?

Yes, the leading zeros are almost always still in the CSV file. A CSV is plain text, so a ZIP code stored as 02134 is five characters on disk. Excel removes the zero only in its own copy, when it decides the column holds numbers. Open the file in Notepad or TextEdit and the zero is there.

That difference between the file and the screen is the most useful thing to know about this problem. Excel reads every field, guesses whether it is a number, a date or text, and turns anything that looks numeric into a number. A number has no leading zeros, so 02134 becomes 2134 in the cell. Microsoft's article on keeping leading zeros and large numbers confirms that Excel removes leading zeros automatically and suggests converting such values to text to keep them.

So the damage at this stage is only on screen. Close the workbook without saving and the CSV is unchanged. The table shows example values and what happens to each. The values are made up for illustration.

The last row is the exception worth noticing. An amount with a leading zero is a formatting accident, and you want Excel to read it as the number 42.5. Text is for identifiers, not money.

Importing zero-padded CSV columns as Text in Excel

Importing with Power Query lets you mark a column as Text before Excel converts it. Choose Data, From Text/CSV, pick the file, and select Transform Data instead of Load. In the editor, select the column with zeros, set its data type to Text under Home, Transform, Data Type, and load the result.

The steps in order:

1. Open a blank workbook. Leave the CSV closed.
2. Choose Data, then From Text/CSV, and select the file.
3. In the preview window, choose Transform Data.
4. Click the header of a zero-padded column, such as the ZIP or check number.
5. Choose Home, then Data Type, then Text. If asked, replace the current conversion step.
6. Repeat for each identifier column, then choose Close & Load.

Power Query adds a "Changed Type" step on its own, and that step is where the zeros vanish. Replacing it, rather than adding a new step after it, is what keeps the original characters.

Other tools have their own switch for the same thing. Google Sheets has an option to stop it converting text to numbers during import. A script reading the CSV can set the column type to string. Converting the data to JSON avoids the guess entirely, because a JSON string in quotes is never read as a number; a transaction JSON schema shows how identifiers are usually typed as strings for exactly this reason. If the JSON later goes back into a spreadsheet, the JSON to Excel guide covers keeping those string columns intact.

Which CSV columns need their leading zeros kept?

Keep leading zeros in any column that names something rather than measures it. ZIP codes, check numbers, account and routing numbers, branch or sort codes, employee and customer IDs, product codes and phone numbers all qualify. If you would never add two of the values together, the column is an identifier and should be Text.

Bank and statement data is full of these. Routing numbers and some account numbers start with zero. Check numbers are often padded to a fixed width. Card references and transaction IDs can be long as well as zero-padded, so Excel may both strip the zero and round the end of the number. Treat each of those as Text from the start.

Columns to leave as numbers are just as clear: amounts, balances, quantities and rates. Dates belong to their own type. Setting everything to Text to be safe creates a new problem, because Text amounts do not sum and Text dates sort as words.

A quick test helps when you are unsure. Look at the longest value in the column. If its length is fixed across every row, like a five-digit ZIP or a nine-digit routing number, it is almost certainly an identifier.

Scanned statements add one more risk to identifier columns. If optical character recognition read a 0 as the letter O, the column may already hold mixed text. When a double-sided paper statement goes through a scanner, the back pages are where faint digits tend to blur; scanning a double-sided bank statement covers settings that keep both sides legible.

Re-saving a CSV after Excel stripped the zeros

Saving the CSV after a plain double-click open is what makes the loss permanent. Excel writes back what its cells hold, and those cells hold 2134, not 02134. The file on disk then matches the damaged view, and the original zeros are gone unless you have another copy of the file.

The habit that prevents this is simple: open CSVs you care about through the import, and if you open one by double-clicking just to look, close it without saving. Excel often asks whether to save changes when you close, even if you only looked. Decline.

If the zeros are already gone, you have two ways back. The better one is to get the original file again, from the bank, the converter or the sender, and import it properly. The second is to rebuild fixed-width identifiers with a formula. For a five-digit ZIP in column A, =TEXT(A2,"00000") returns the value with its zeros restored. This works only when every value in the column should have the same length, so it suits ZIP codes and padded check numbers but not identifiers of varying length.

A custom number format such as 00000 makes the zeros show again on screen, but the cell still holds a number, and the next person to open the file by double-clicking meets the same problem. A Text column is the lasting fix.

FastBankConvert is getting ready to open, with CSV, Excel and JSON as its output formats. Get started adds you to the list for trial conversions.