If a CSV contains identifiers such as 00123 or a 16-digit account number, import the relevant columns as text before loading the file. Opening a CSV directly can make Excel remove leading zeroes, display long numbers in scientific notation, or change values that resemble dates. Check the import preview against the source before you load.
Why Excel changes CSV identifiers
A CSV stores rows of delimited fields, but it does not reliably tell Excel what each field means. When you open a CSV directly, Excel may infer that a digit-only field is a number. That is useful for arithmetic, but risky for identifiers whose exact characters matter.
As an Amazon Associate I earn from qualifying purchases.
For example, Excel may interpret 00123 as the number 123, or display a long numeric string in scientific notation. Microsoft says Excel removes leading zeroes and converts large numbers to scientific notation so it can perform calculations. Excel also has a maximum numeric precision of 15 significant digits; longer digit strings need text treatment if every digit must be preserved. See Microsoft’s guidance on keeping leading zeroes and large numbers.
Recommended Free Tools
Use numbers when arithmetic is meaningful. Use text when the value is a label or key, even if it contains only digits. Common examples include postal codes, product codes, account numbers, phone numbers, and long IDs. Strings with an “E” between digits or that resemble dates can also be converted under Excel’s automatic conversion rules.
#1 Best Overall
- Over 215 Microsoft Windows Excel Shortcuts
- Two-Sided Durable Laminiated Sheet
- Designed for Excel on a Windows Computer
Import the CSV with identifier columns set to Text
- In Excel, select Data > From Text/CSV, then choose the CSV file. Do not assume that opening the file directly, or looking at an initial preview, preserved every identifier.
- In the import preview, select Transform Data or Edit to open the transformation editor. Microsoft’s text and CSV import instructions describe this route.
- Select each identifier column, then choose Data Type > Text. If Excel asks whether to replace the inferred type, choose to replace it. For a repeatable import, confirm the query includes a type-conversion step for that column. Microsoft’s leading-zero and large-number guidance covers setting columns to text.
- Check representative rows in the preview against the original CSV, including IDs with leading zeroes, 16 or more digits, date-like text, or an “E” between digits. When they match, select Close & Load.
- If you refresh the imported data later, confirm the saved type-conversion step remains in the query. Refresh applies the saved transformations.
Menu labels and feature availability vary by Excel version and platform. Microsoft’s documented workflow applies to the products listed on its support pages; it does not guarantee identical controls in every build.
Other ways to prevent automatic conversion
| Method | Best fit | Trade-off |
|---|---|---|
| From Text/CSV with an explicit Text type | Imports that need to be controlled, checked, or repeated | Requires using the import and transformation flow and checking column types. |
| Automatic Data Conversion settings | Preventing common conversions across text entry and loading | Available only in documented newer Excel releases; settings are broad, so check which controls your version offers. |
| Legacy Text Import Wizard | A compatibility workflow that needs explicit per-column formats | The legacy wizard may need to be enabled first. |
| Worksheet cells set to Text before entry or paste | Values being entered or pasted into a prepared worksheet | Must be set before the values arrive; changing the format afterward cannot undo conversions. |
Automatic Data Conversion settings
In versions that offer them, these settings let you disable individual conversions, including removing leading zeroes, limiting long numeric data to 15 digits, interpreting digits around “E” as scientific notation, and converting continuous letter-number strings to dates. Availability depends on the Excel version. Microsoft’s data import and analysis options describe the settings. Treat them as an extra safeguard, not a substitute for explicitly setting identifier columns to Text in a particular import.
Legacy Text Import Wizard
To enable the wizard, go to File > Options > Data and enable From Text (Legacy). Then choose Data > Get Data > Legacy Wizards > From Text (Legacy). In the wizard’s final step, select each identifier column and set its format to Text. Microsoft retains this wizard for compatibility and documents it on its Text Import Wizard page.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Text format before typing or pasting
For values going into a worksheet rather than an imported CSV, format the destination cells as Text before entering or pasting the data. This can prevent Excel from interpreting the incoming values as numbers or dates. Microsoft’s instructions for formatting numbers as text explain the distinction between setting a format in advance and changing it after entry.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Check for changes and recover from the original
Compare representative imported IDs directly with the untouched CSV in a plain-text editor or another source that does not reinterpret the values. Check for missing leading zeroes, changed final digits in long values, scientific notation, and date-like strings that have become dates. The worksheet display alone may not establish that a long value is intact, so compare the stored result with the source.
If the original CSV is intact, import it again and set affected columns to Text before loading. Applying Format Cells > Text after conversion does not restore removed zeroes or digits. A custom number format can display leading zeroes for a known-width code, but it changes appearance rather than recovering characters that are no longer in the value.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
