CSV losing leading zeros in Excel? Here is the fix
Excel drops leading zeros (07123456789 becomes 7123456789) because it reads the column as numbers. Keep them by setting the column to Text during import: Data > From Text/CSV, or the legacy Text Import Wizard, or by formatting cells as Text before pasting. Zeros already lost cannot be recovered by changing the format.
When Excel opens a CSV, phone numbers like 07123456789 and codes like 01234 lose their leading zero because Excel reads digit-only columns as numbers. The fix is to import that column as Text, or format the cells as Text before you paste.
Why the zeros disappear
A CSV is plain text, so the zeros are still inside the file. Excel uses its default data format for each column when it opens the file, and a column of digits becomes a Number, which does not keep leading zeros. Microsoft says that to keep them you convert such a column to Text during import.
If the zeros are already gone in your sheet, changing the format to Text will not bring them back. Undo, or re-import the original CSV with the steps below.
Fix 1: Import with Data > From Text/CSV
- Open a blank workbook in Excel.
- Go to the Data tab and click From Text/CSV.
- Pick your CSV file and click Import.
- In the preview, click Transform Data.
- Select the phone or code column, then on the Transform tab set Data Type to Text. If Excel asks to replace the current step, choose to replace it, so the zeros are not stripped first.
- Click Close and Load.
Fix 2: The legacy Text Import Wizard
Excel does not run the wizard when you simply double-click a .csv. Microsoft's documented ways to force it are to change the file extension from .csv to .txt before opening it, or to enable the legacy wizard.
- Go to File > Options > Data and tick From Text (Legacy) under the legacy data import wizards.
- On the Data tab choose Get Data, then Legacy Wizards, then From Text (Legacy), and pick your file.
- Choose Delimited, tick Comma as the delimiter, and click Next until Step 3.
- Click the column that holds the phone numbers or codes and choose Text under Column data format.
- Repeat for every column that needs its zeros, then click Finish.
Need the data in another format instead? Try the CSV to PDF converter for a printable table. For JSON, check that numbers are not converted: the CSV to JSON converter converts numbers, so test one row with a leading zero first.
CSV to JSON
Values with a leading zero, such as 00417, stay as text here. Test one row before relying on it.
Runs in your browser. Nothing you type is sent to our server. Open the full CSV to JSON page
Fix 3: Format as Text before pasting
- Select the whole column where the numbers will go.
- Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells.
- On the Number tab choose Text and click OK.
- Paste your values. Excel keeps them as typed, zeros included.
When you type a number straight into an Excel cell, a leading apostrophe (like '07123456789) makes Excel treat it as Text. Do not add apostrophes inside a CSV file: they usually show up as a visible character in the cell.
Which fix should you use?
| Situation | Best option | Why |
|---|---|---|
| You have a CSV file on disk | Data > From Text/CSV, set the column to Text | The type is set before Excel converts the digits |
| You want the old step-by-step screens | Legacy Text Import Wizard | Column data format is chosen per column in Step 3 |
| You are copying values from another window | Format the cells as Text, then paste | Excel keeps pasted values as they are |
| Zeros are already lost and the length is fixed | Rebuild with a formula | Quick repair when you cannot find the original file |
Repairing zeros you have already lost
Always try to re-import the original CSV first, because that gives you the true values. If the original is gone and every value should have the same length, you can pad the numbers back. For a phone number that should be 11 digits, type this in an empty column and fill it down:
=TEXT(A2,"00000000000")
Use as many zeros as the full length. This only works when every value in the column has the same length. For a mixed column, such as codes of different lengths, do not guess: go back to the source file. Copy the formula results and use Paste Special > Values to turn them into fixed text.
Columns that should stay Text
Any value that is an identifier and not an amount should be Text. Typical examples are phone numbers, PIN codes, employee or student IDs, account references and product codes. You will never add or average them, so there is nothing to lose by keeping them as Text. Real amounts, quantities and dates should stay as numbers or dates so that sums and sorting work.
A good habit is to look at the first few rows right after importing. If a value is aligned to the left of the cell, Excel is treating it as Text. Values aligned to the right are usually numbers, which is a hint that the zeros may already be gone.
Saving back to CSV
If the cells are Text, the zeros are written to the CSV. But the next person who double-clicks that CSV will lose them again. Share an .xlsx instead, or tell them to import the column as Text.
Common mistakes
- Changing the column to Text after the data is already open and expecting the zeros to return.
- Double-clicking the CSV and assuming Excel will ask about column types. It will not.
- Setting only one column to Text when several columns (phone, PIN code, ID) need it.
- Saving over your original CSV before checking the zeros. Keep a copy of the original file.
For other office conversion problems, see how to fit an Excel sheet on one PDF page.
Tools for this
Frequently asked questions
Why does 07123456789 become 7123456789 in Excel?
Excel reads a column of digits as numbers, and numbers have no leading zeros. The zero is still in the CSV file, but Excel drops it when it opens the file. Import the column as Text to keep it.
Can I get the leading zeros back after opening the CSV?
Not by changing the format to Text, because the zeros are already gone from the cells. Re-import the original CSV with the column set to Text, or rebuild the value with a formula such as =TEXT(A1,"00000000000") if you know the fixed length.
Why does double-clicking a CSV not show the import wizard?
Excel opens a .csv using its default data formats. Microsoft's guidance is to run the Import Text Wizard by changing the extension to .txt before opening, or to use Data > From Text/CSV or the legacy wizard.
I have thousands of rows. Do I fix each cell?
No. Set the data type of the column once during import and it applies to every row in that column.
Should I add an apostrophe in the CSV file?
No. An apostrophe works when you type into a cell in Excel, but inside a CSV it usually appears as a visible character. Use the import steps instead.
Will the zeros survive when I save as CSV?
If the cells are Text, they are saved with the zeros. The next person opening the CSV directly in Excel can lose them again, so share an .xlsx if the zeros matter.
Sources
- Microsoft Support: Import or export text (.txt or .csv) files (checked 2026-10)
Rules and limits change. Always check the latest official notification or portal before you submit a form.