Skip to content
AnIntent

How to Keep Leading Zeros in Excel (Custom Format, Import, or Setting)

Stop Excel stripping zeros from zip codes and turning long numbers into E+15, with a custom format, a Power Query Text column, or one setting.

AnIntent Editorial · · 5 min read

laptop computer on glass-top table
Photo by Carlos Muza on Unsplash
In this article
  1. Pick the fix that matches your data
  2. Use a custom format for zip codes and other short codes
  3. Import CSV and text files with the column set to Text
  4. Turn off automatic conversion in Microsoft 365 or Excel 2024
Share this article

You can keep leading zeros in Excel, and stop 16-digit codes turning into 1.23E+15, with one of three fixes Microsoft documents. Which one you need depends on where the data comes from and how long the codes are.

Pick the fix that matches your data

  • Short number codes you type, like zip codes: a custom number format such as 00000. It works for codes with fewer than 16 digits.
  • Data you import from a CSV, text, XML, Web or JSON source: set the column to Text in Power Query.
  • Anything you type, paste or open in Excel for Microsoft 365 or Excel 2024: switch off the automatic conversion options in Excel's settings.

The reason Excel does this at all: it removes leading zeros from entries like 00123 and converts large numbers like 1234 5678 9087 6543 to scientific notation like 1.23E+15 so that formulas and math work on them, according to Microsoft's page on keeping leading zeros and large numbers. That page lists social security numbers, phone numbers, credit card numbers, product codes, account numbers and postal codes as the usual victims.

Use a custom format for zip codes and other short codes

This keeps the value a number but shows the zeros. Microsoft recommends it when the workbook isn't used as a data source by other programs, and it works for number codes with fewer than 16 digits, per Microsoft Support.

  1. Select the cells.
  2. Press Ctrl+1 to open Format Cells.
  3. On the Number tab, choose Custom in the Category list.
  4. Type the format in the Type box.

Each 0 in the format is one digit position. Microsoft's examples:

Code You type Custom format Excel shows
Postal code 00123 00000 00123
Social security 012345678 000-00-0000 012-34-5678
Phone 0012345556789 00-0-000-000-0000 00-1-234-555-6789

For a five-digit US zip code, the format is simply:

00000

If you don't want to write your own, the Special category in the same dialog offers Zip Code, Zip Code + 4, Phone number and Social Security Number. Microsoft's note on this tip says the formatting does not bring back zeros that were already removed; it affects numbers entered after you apply it. So format the column first, then type or re-enter the codes.

Why this fails for credit card numbers

A custom format can't save a 16-digit code. Excel keeps only 15 digits of precision for numbers, and Microsoft's automatic conversion page says converting to a numeric format such as Currency always truncates to 15 significant digits. Microsoft cites a 16-digit credit card number as the common case. For codes that long, the value has to be stored as text, so use one of the next two methods.

Import CSV and text files with the column set to Text

If the zeros vanish when you open a CSV, bring the file in through Power Query instead and tell Excel the column is text. Microsoft's steps use a text file and say they are the same for XML, Web and JSON sources (Microsoft Support):

  1. On the Data tab, choose From Text/CSV, next to Get Data.
  2. If you have no Get Data button, go to New Query > From File > From Text, browse to the file, and press Import.
  3. Press Edit in the preview pane to open the Query Editor.
  4. Click the header of each column to convert. Ctrl+click selects several. Then go to Home > Transform > Data Type > Text.
  5. In the Change Column Type dialog, choose Replace Current.
  6. Choose Close & Load.

When the source file changes, use Data > Refresh and Excel reapplies the same transformations, so the codes stay intact.

This is also the method to use for 16-digit and longer codes in imported data, because the column never becomes a number.

Turn off automatic conversion in Microsoft 365 or Excel 2024

If you're on Excel for Microsoft 365, Microsoft 365 for Mac, Excel 2024 or Excel 2024 for Mac, there's a setting that stops the stripping at the source, according to Microsoft's automatic data conversions page.

Windows: File > Options > Data, then find Automatic Data Conversion.

Mac: Excel > Preferences > Edit, then find Automatic Data Conversion.

The two options that matter here:

  • Removing leading zeros from numerical text and converting to a number. Clear it and 00123 stays as text instead of becoming 123.
  • Keeping the first 15 digits of long numbers and showing scientific notation. Clear it and 12345678901234567890 stays as typed, instead of becoming 12345678901234500000 or 1.23457E+19.

The same panel has a third option for digits around the letter E (so 123E5 isn't turned into 1.23E+07) and a fourth for converting letter-and-number strings to dates.

Microsoft says these settings apply when you open a .csv or .txt file, type data, paste from external sources, use find and replace, or run the Convert Text to Columns wizard.

Limits of the setting

  • It doesn't directly affect data imported by Power Query, which has its own type settings. Use the Power Query steps above for that.
  • Numbers kept as text may not work in math. If you need zeros shown but still want a real number, Microsoft says to use a custom cell format instead.
  • Older versions of Excel, before 2024, don't have this setting. On those, use a custom format for short codes and Power Query for imports and long codes.

If you later need to build formatted strings from numbers in formulas, see how to use the TEXT function in Excel, and if imported codes come with stray spaces, TRIM and CLEAN will tidy them before you match on them.

Common questions

Why does Excel drop the leading zero from my zip code?

Excel treats 00123 as a number and removes the leading zeros so formulas and math work on it. A custom format like 00000 shows the zeros again.

How do I stop a 16-digit number turning into E+15?

Store it as text: import the column as Text in Power Query, or in Microsoft 365 or Excel 2024 turn off the option that keeps the first 15 digits and shows scientific notation. A custom number format won't work because numbers are limited to 15 significant digits.

Does the Automatic Data Conversion setting fix Power Query imports?

No. Microsoft says those options don't directly affect data imported by Power Query, which has its own data type settings.

Which Excel versions have the Automatic Data Conversion setting?

Excel for Microsoft 365, Microsoft 365 for Mac, Excel 2024 and Excel 2024 for Mac, per Microsoft's support page.

Hands typing on a laptop displaying a data spreadsheet
How-to

How to Use LET in Excel to Stop Repeating the Same Calculation

Use Excel's LET function to name a value or calculation once, reuse it inside a formula, and have Excel calculate it only one time.

· 4 min

More in Cloud & Productivity Software