Excel Removes Leading Zeros from CSV? Import IDs as Text

Happy N Yummy Tech · Data you can trust

Import the identifier column as text before Excel turns it into numbers. If an earlier import already changed the characters, return to the intact source file and import again.

A shield protects the two leading zeros in the identifier 00123 above a conceptual spreadsheet grid.
AI-generated conceptual illustration, not an Excel screenshot.

An ID is a label: 00123 and 123 may identify different records. This guide gives you a small practice file and an exact-text check, so success means preserving the characters—not merely making a cell look right.

Try the problem with harmless sample data

View the sample text file. It uses comma-separated fields and invented product references. Keep an unchanged copy; import that copy through Excel's text/CSV workflow.

id,label
00123,Blue mug
00007,Measuring spoon
1234567890123456,Long reference

The third reference deliberately has 16 digits. Microsoft's identifier-preservation guidance explains both leading-zero conversion and Excel's 15-digit numeric precision limit. Preserve identifier strings as text rather than relying on a number display format.

Import the original values before changing their type

  1. Open a blank workbook. Use Data → From Text/CSV and select the sample file. Names and availability differ across Excel editions; use your edition's text-import option.
  2. Confirm that the comma is the delimiter and that the preview has separate id and label columns.
  3. If the preview offers a data-type detection setting, turn automatic detection off. Otherwise open Transform Data and inspect the query steps before loading.
  4. Keep the id column as Text. If an automatic type-changing step has already interpreted the IDs as numbers, return to the original text step and replace that conversion with a text type. Converting an already damaged number back to text cannot recreate missing characters.
  5. Load the data and compare all three IDs with the expected values below. Save the working workbook separately from the original text file.

Power Query can infer types for text files. Its Text/CSV documentation describes the import settings, and the data-type reference explains automatically added conversion steps.

Edition boundary: these steps are documentation-checked, not a claim of a live test in every Excel release. If your edition lacks this import screen, use its text-import wizard and select Text for the ID column before completing the import.

Expected results

RowExact IDCharacter countWhat this checks
1001235Two initial zeros survive
2000075Four initial zeros survive
3123456789012345616Every digit survives

Check all three IDs with Excel EXACT

With the sample loaded into columns A:B and the header in row 1, enter these formulas in empty cells C2:C4. Each result should be TRUE:

C2: =EXACT("00123",A2)
C3: =EXACT("00007",A3)
C4: =EXACT("1234567890123456",A4)

Microsoft documents EXACT as a case-sensitive comparison of two text strings. It ignores display formatting, so matching what a cell appears to show is not enough. A numeric 123 displayed with a five-digit number format is not the original string 00123.

OriginalValue being comparedExpected result
0012300123TRUE
00123123FALSE
0000700007 (trailing space)FALSE
12345678901234561234567890123450FALSE

Some regional settings require semicolons instead of commas. These checks compare the sample characters; they do not audit every cell or prove the column's stored data type. Inspect the Text type during import as well.

If the result still differs

  • The first zeros disappeared: inspect the original file and the type-conversion step. Do not guess a padding length when different identifiers may have different valid lengths.
  • A long identifier changed: re-import from the unchanged source. Adding zeros or changing the display format does not prove that the source digits were recovered.
  • The text looks identical but EXACT returns FALSE: inspect spaces and row order. Spaces are characters too; check the source before trimming them.
  • All three formulas return TRUE: the compared values match this sample. That does not certify the entire workbook; sample additional rows from your actual data and verify the final exported file when another system will consume it.

A small habit that prevents larger cleanup work

Before importing real data, write down which columns are identifiers and which are quantities. Keep the source unchanged, import those identifier columns as text, then compare a few deliberately awkward records: a leading zero, the longest ID, and an unusual letter-number combination. This turns “the file opened” into a checkable result.

If you will use the imported IDs in a lookup, continue with our XLOOKUP and VLOOKUP comparison. Check that the lookup value and source column use compatible representations before comparing formulas.

What this guide verifies: the supplied sample text, expected comparisons and Microsoft documentation. An Excel UI walkthrough in your specific version remains a separate check.

Copyable practice file

Copy this example into a plain-text file. Its records are synthetic.

id,label
00123,Blue mug
00007,Measuring spoon
1234567890123456,Long reference

Previous Post Next Post

Review or withdraw your advertising consent where available.