Quick answer: To keep leading zeros in an Excel CSV import, import the file through Data → From Text/CSV and set identifier columns to Text before loading them. Avoid relying on a double-click to open the CSV, because automatic conversion can turn 00123 into 123.
Practical example & checks
Import a practice CSV and verify the identifiers
customer_id,quantity
00123,4
00007,2
123456789012345678,1Use a new workbook. Choose Data → From Text/CSV and the transform/import controls available in your Excel version. Set customer_id to Text before loading; quantity can remain numeric.
| Field | Expected after import |
|---|---|
| First customer_id | 00123, including two leading zeros |
| Second customer_id | 00007, including four leading zeros |
| Third customer_id | All 18 digits unchanged |
| Sum of quantity | 7 |
Open the imported cells and inspect the stored values, not only the display. A custom number format may display zeros without preserving the original identifier. If automatic conversion already removed digits, re-import from the original CSV; changing a damaged cell to Text does not reconstruct information that was lost.
Original ThinkQuarry teaching material added 19 October 2026. Hypothetical examples are not product-test results.
Postal codes, account references and inventory IDs can look numeric without being numbers you should calculate with. For an identifier, each character matters. Treating these fields as text protects their meaning rather than merely changing how a number appears.
Separate identifiers from quantities
| Field | Example | Recommended interpretation |
|---|---|---|
| Customer ID | 00123 | Text |
| Postal code | 01234 | Text |
| Order quantity | 12 | Number |
| External reference | 000009876543210 | Text |
Ask whether arithmetic on the field makes sense. Adding two customer IDs does not produce a meaningful business result. A phone number also carries conventions such as a country code and optional leading plus sign, so it usually belongs in a text column.
Import with an explicit text type
- Keep the original CSV unchanged.
- Open a blank Excel workbook.
- Choose Data → From Text/CSV. In some versions the path appears under Get Data → From File.
- Select your CSV and inspect the delimiter preview.
- Choose Transform Data when available.
- In Power Query, select the identifier column and set its data type to Text. Inspect any automatic Changed Type step; remove or edit a numeric conversion that already altered the identifier.
- Load the result into the workbook and compare sample values with the raw CSV.
Menu names vary by Excel version and platform. Microsoft also documents a legacy text import workflow. The essential requirement is the same: prevent numeric interpretation before the original characters are lost.
Why number formatting is not the same fix
A custom number format such as 00000 can display 123 as 00123. That may be useful when every identifier has a known fixed width, but it does not establish what the original data contained. The displayed zeros are formatting, while a text value stores the characters themselves.
If a file contains both 00123 and 000123, turning both into the number 123 loses the distinction. Padding everything to five characters cannot recover which original value belonged to which record.
Recover from an already damaged import
If you still have the original CSV, close the damaged working copy and re-import the source with the correct text type. If the source was overwritten, look for a previous export, backup or the system that generated the data.
Use a padding formula only when you have an authoritative rule for the field’s length. For a known five-character numeric code, =TEXT(A2,"00000") creates five-character text. Do not apply that rule to a mixed list simply because the output looks tidy.
Check before sending a CSV back out
Save an XLSX working copy to preserve the workbook. If you need a CSV, export a separate delivery copy. Then inspect a few identifiers in a plain-text editor. Opening the exported CSV by double-clicking it can trigger conversion again and make a correct file appear damaged inside Excel.
A useful acceptance set includes a short leading-zero ID, the longest reference in the dataset, a code with letters, an empty identifier and an international phone number. Check these against the source, not against a memory of what the cells looked like.
Try a small practice CSV
Copy this fictional sample into a plain-text file named leading-zero-practice.csv. Import it using the steps above and set customer_id and postal_code to Text. Keep quantity numeric.
customer_id,postal_code,quantity
00123,01234,2
000123,00482,5
A0123,00127,1Your acceptance check: the first two customer IDs must remain different, all postal codes must retain five characters, and the quantities must still support calculation. After exporting a delivery CSV, inspect the file in a text editor before reopening it in Excel. If the first two IDs both become 123, return to the original file and review the automatic type-conversion step.
Common questions
Does adding quotes around CSV values solve the problem?
CSV quotes define field boundaries. They are not a reliable instruction to every importer to treat a field as text. Configure the importer’s data type explicitly.
Will changing a damaged column to Text bring the zeros back?
No. It changes the type of the remaining value. Re-import the original characters or reconstruct them only from a verified formatting rule.
Should I store all spreadsheet data as text?
No. Keep quantities and amounts numeric when you need calculations. Use text for identifiers whose characters must remain exact.
For a Sheets workflow, see fixing CSV import separators and keeping records together during sorting.
Sources and further reading
Prepared by ThinkQuarry. Documentation checked 3 October 2026. Menu labels may vary by version. Featured image: original AI-generated photographic concept created for ThinkQuarry. This editorial visual is not a product screenshot.
