Keep Leading Zeros and Prevent Scientific Notation in Excel
Question details
Preserve leading zeros and prevent scientific notation when entering or importing identifiers in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user needs to import or enter identifiers such as 12-digit UPCs without changing their original values.
- Observed behavior
- Excel may treat identifiers as numbers, remove leading zeros, or display long values in scientific notation.
Check the original CSV or other source to confirm which columns contain identifiers and how many digits they should have. Avoid opening a CSV by double-clicking it, because Excel may automatically convert the values before you can assign a text type.
Import the identifier column as text
Use this method for CSV files to preserve the original identifier values during import.
In Excel, select Data > From Text/CSV, choose the CSV file, and select Import.
In the preview window, select Transform Data. If the Applied Steps pane contains an automatic Changed Type step, remove that step before setting the identifier column type.
Select the UPC or identifier column, then choose Text from the Data Type menu in Power Query.
Select Home > Close & Load, then compare the imported identifiers with the original source to confirm that leading zeros remain.

Format cells as text before entering identifiers
Use text formatting when typing or pasting identifiers into a worksheet.
Hide the number-stored-as-text warning
Use this option when Excel flags identifiers that are intentionally stored as text.
Import identifiers as text in WPS Spreadsheets
Use the text import settings in WPS Spreadsheets to keep leading zeros and prevent identifiers from being displayed as scientific notation.
- 1. Open the import tool: In WPS Spreadsheets, open the Data tab and select Import Data or From Text/CSV, depending on your version.
- 2. Choose the CSV: Select the source CSV file and use the import wizard to confirm the delimiter and preview the columns.
- 3. Set the identifier type: Select the UPC or identifier column in the import wizard and set its column data format to Text.
- 4. Finish and verify: Complete the import, then compare the imported identifiers with the source. Use File > Save As to save the worksheet as an XLSX file if needed.

Frequently Asked Questions
Why does Excel remove the leading zero from a UPC?
Excel may interpret the UPC as a number, for which leading zeros have no numeric value. Import the UPC column as Text using Data > From Text/CSV > Transform Data.
Can I recover zeros by changing an existing column to Text?
Changing the format after conversion does not automatically restore missing zeros. Reimport the original file as text. If the required length is known, a formula can add padding, but verify the results against the source.
Why does Excel show an identifier in scientific notation?
Excel may display a long value in scientific notation when it treats the value as a number. Importing the identifier as Text preserves its characters rather than displaying it as a numeric value.
Should I put an apostrophe before every UPC in the CSV?
No. An apostrophe written into a CSV may become part of the imported value. Instead, select Text for the identifier column in the import settings.
How do I remove the green triangle from text identifiers?
Select the flagged cells, click the warning icon, and choose Ignore Error. This dismisses the warning without converting the identifiers to numbers.




