Data Import & Export

Keep Leading Zeros and Prevent Scientific Notation in Excel

Ayan MasoodAyan Masood Nov 22, 2026 868 views

Question details

Preserve leading zeros and prevent scientific notation when entering or importing identifiers in Excel.

Keep Leading Zeros and Prevent Scientific Notation 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.
Before you start

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.

Solution 1Recommended

Import the identifier column as text

Use this method for CSV files to preserve the original identifier values during import.

1
Open the CSV import tool

In Excel, select Data > From Text/CSV, choose the CSV file, and select Import.

2
Transform the data

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.

3
Set the column to text

Select the UPC or identifier column, then choose Text from the Data Type menu in Power Query.

4
Load and check the values

Select Home > Close & Load, then compare the imported identifiers with the original source to confirm that leading zeros remain.

Import the identifier column as text
Import from the original source: If Excel has already removed zeros or changed digits, changing the column to Text afterward will not necessarily restore them. Reimport the original data with the correct type.
Preserve identifiers in WPS

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. 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. 2. Choose the CSV: Select the source CSV file and use the import wizard to confirm the delimiter and preview the columns.
  3. 3. Set the identifier type: Select the UPC or identifier column in the import wizard and set its column data format to Text.
  4. 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.
Import UPC and identifier columns as textOpen and save Microsoft Excel XLSX filesWork with CSV data in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.