To keep leading zeros when opening a CSV in Excel, do not double-click the file. In Excel for Microsoft 365 on Windows, go to Data > From Text/CSV, choose the file, select Transform Data, set the affected column’s data type to Text, and then choose Close & Load. A CSV stores values as plain text without reliable column-type instructions, so Excel may otherwise read an identifier such as 00123 as the number 123.
Why Excel removes leading zeros
A CSV file is a text file in which fields are separated by commas or another delimiter. It does not reliably tell spreadsheet software whether a field is a number, a date, a ZIP code, or an account identifier. When Excel opens a CSV directly, it tries to interpret each field. If it sees 00123, it may treat that value as the number 123 because leading zeros do not change a number’s mathematical value.
This automatic interpretation is often helpful for ordinary calculations, but it is a problem for identifiers. A postal code, SKU, customer number, or phone extension can contain digits without representing a quantity. For these values, 00123 and 123 may identify different records. Excel’s conversion can therefore cause failed lookups, mismatched records, or incorrect data when the file is used elsewhere.
The zeros are usually not deleted from the original CSV just because Excel displays a converted value. The risk comes when you save the changed worksheet over the original or export the converted values to another file. Keep an untouched copy of the CSV until you have checked the imported data.
Import the CSV and assign the column as text
The most reliable desktop method is to import the file and explicitly set identifier columns to Text before loading them into the worksheet. In Excel for Microsoft 365 on Windows:
- Open a blank workbook or the workbook where the data should go.
- Select Data > From Text/CSV, browse to the CSV, and choose Import.
- In the preview window, choose Transform Data rather than loading the file immediately.
- In Power Query Editor, select the column that contains values such as postal codes or product IDs.
- On the Home tab, open the Data Type menu and choose Text. If Excel has already created a step that changed the column to a number, change or remove that step and set the column to Text.
- Choose Home > Close & Load to bring the data into Excel.
Set every relevant identifier column to Text, not just the first one. For example, a file might contain a ZIP code, a branch code, and a customer reference in separate columns. Check the preview and the loaded table to confirm that values such as 00123 remain unchanged. If the CSV uses semicolons, tabs, or another delimiter instead of commas, confirm that the preview has split the fields into the correct columns before loading.
Excel for Mac also offers text/CSV import through its Data or Get Data tools in supported Microsoft 365 versions, though the exact menu wording and Power Query availability can vary by build. Excel for the web has fewer import and transformation controls than desktop Excel. If you cannot assign the column’s type during import, use desktop Excel or a text-aware import workflow rather than opening the CSV directly.
Why double-clicking the file is risky
When you double-click a CSV in File Explorer, Excel opens it using its default interpretation rules. You may not get an import preview or a chance to specify that a particular column is text. A column of identifiers can consequently lose its leading zeros as soon as Excel displays the data. The same concern applies to values Excel recognizes as dates, long numbers, or scientific notation.
Changing the cell format after opening is not always enough. If Excel has converted 00123 to the number 123, applying the Text format afterward does not restore the missing zeros; it only changes how future entries are treated. The original width or intended number of zeros is not necessarily recorded in the converted cell. That is why choosing Text during import is safer than trying to repair the appearance afterward.
If you have already opened the CSV and the zeros appear missing, do not save over the source file. Close it without saving and import the original using the text-column method. If the original is unavailable, recovery may still be possible when you know the required width, but you must verify the rule against a trusted source.
Text format, custom number formats, and apostrophes
Text is usually the right data type for codes that only look numeric. Excel preserves the characters as entered, so 00123 remains 00123, and the value is not treated as a quantity for arithmetic. This is normally appropriate for ZIP codes, product numbers, and identifiers that need exact matching.
A custom number format is a different option when the values really are numbers and every value should display at a known width. For example, formatting the number 123 with the custom format 00000 displays 00123. The underlying value remains 123, however, and the zeros are a display rule rather than part of the stored value. That can be useful for presentation inside Excel, but it is less suitable when software expects the actual identifier characters.
Custom formatting also may not travel with a CSV export. CSV files do not preserve Excel’s cell formatting, so a number displayed as 00123 can be written as 123 when exported. If another system needs the padded characters, convert or store the identifier as text and inspect the exported CSV in a text editor before sending it.
For a one-off entry, prefixing a value with an apostrophe in Excel, such as ‘00123, tells Excel to treat the entry as text; the apostrophe is not displayed as part of the cell value. This can help with manual data entry, but it is not a convenient solution for importing a large CSV, and it is not a substitute for setting the column type during import.
Can you restore zeros after Excel has removed them?
Sometimes, but only if you know what the original values should look like. If every code has exactly five characters, the number 123 can be displayed or reconstructed as 00123. With a numeric value in A2, the formula =TEXT(A2,"00000") returns the text value 00123. The five zero placeholders mean “show at least five digits,” adding zeros to the left when needed.
This approach assumes that all identifiers should have a five-character width. It is not safe when lengths vary or when the zeros are meaningful but follow different rules. A value that appears as 123 might originally have been 0123, 00123, or simply 123. Excel cannot infer which version was intended from the number alone. Check an original source, specification, or known examples before rebuilding values.
If you use the formula for recovery, fill it down in a new column and compare the results with trusted records. Once verified, you can copy the results and use Paste Special > Values if you need fixed text values. Retain the original imported data and document the width rule so the repair is understandable later.
Check the import before saving or sharing
Before using the imported data, inspect representative values from the top, middle, and bottom of the file. Check short codes with leading zeros, long identifiers, blank cells, and any fields that could be interpreted as dates. A quick visual scan can catch a problem, but also compare counts or sample records with the source system when the data matters.
- Confirm that each field landed in the correct column and that the delimiter was recognized correctly.
- Check that zero-padded values remain visibly intact and are stored as text where appropriate.
- Look for scientific notation or rounded digits in long identifiers.
- Save your working copy as an Excel workbook (.xlsx) if you need to preserve query steps, data types, or formatting.
- If you must produce another CSV, export a copy and reopen that exported file as plain text to verify the actual characters.
Saving as .xlsx preserves Excel features such as cell types and formatting; saving as CSV does not. A CSV export also saves only the active sheet and can discard workbook features. Treat the CSV as a data exchange format, not as a format that preserves all worksheet decisions. When a receiving system has its own import rules, confirm the expected delimiter, encoding, and identifier format with that system.
Common misconceptions and related cases
Putting quotes around a field in the CSV does not reliably force Excel to keep it as text. A field written as "00123" is still a textual field in the CSV syntax, but Excel may interpret the contents as a number when opening the file. Explicitly assigning the column type during import is more dependable than relying on quotes.
Not every value containing digits needs text treatment. Quantities such as units sold or prices should usually be numbers if you plan to calculate with them. The key question is whether the digits describe a measurable amount or identify something. ZIP codes, telephone numbers, and account references are generally identifiers; they should not be summed, and their character patterns may matter.
Long numeric-looking identifiers deserve special attention too. Excel’s numeric precision is limited to 15 significant digits, so long strings of digits may be altered if interpreted as numbers. Importing them as Text protects both leading zeros and the full character sequence. Do not assume that a value is safe just because it has no zeros at the beginning.
Frequently Asked Questions
How do I open a CSV in Excel without changing the data?
Use Data > From Text/CSV, choose Transform Data, set identifier columns to Text in Power Query, and load the result. Avoid double-clicking the CSV when Excel’s automatic conversion could affect important values.
Does putting a zero in front of a number work?
Typing an apostrophe before a value can preserve it as text for an individual entry, but it is not an efficient bulk-import method. For a CSV, set the column to Text during import.
Will saving the workbook as CSV keep the leading zeros?
Only if the values being exported contain the zeros as text. A custom number format may show zeros in Excel without storing them in the underlying value, and CSV does not preserve that formatting. Check the exported file before relying on it.
Can I recover zeros Excel already removed?
Only when you know the intended pattern, such as a fixed five-character code. A text formula can pad values to that known width, but the original number alone cannot reveal how many zeros were removed.
Should ZIP codes and phone numbers be stored as text?
Usually, yes. They identify locations or contact numbers rather than quantities for calculation, and they may contain leading zeros or other formatting characters. Keeping them as text helps preserve the exact values.
Related
- Microsoft Access vs Excel: Which Should You Use?
- Microsoft Access vs Excel: Which Tool Fits Your Data?
- Merge Excel Sheets to Find Specific Names in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find Range Names in Excel: A Practical GuideTo find range names in Excel, open Formulas > Name Manager to review the workbook’s defined names, or…
- How to Clear Cache in Outlook EmailTo clear Outlook’s cache, use the method for the version you have: rebuild the Outlook data cache in…
- How to Insert a Green Checkbox in ExcelTo add a clickable checkbox in Microsoft Excel for Microsoft 365 on Windows, select the target cell or…
- How to Get Rid of a Page Split in ExcelTo remove a page split in Excel, first identify what you are seeing: for a print-page boundary, go…
- MS Word Data Table: How to Create, Format, and Use ItIn Microsoft Word for Microsoft 365, make a data table by choosing Insert > Table, then selecting the…
- How to Find Duplicate Names in an Excel ColumnTo find duplicate names quickly, select the name cells and choose Home > Conditional Formatting > Highlight Cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.