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.
- Select the cells.
- Press Ctrl+1 to open Format Cells.
- On the Number tab, choose Custom in the Category list.
- 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):
- On the Data tab, choose From Text/CSV, next to Get Data.
- If you have no Get Data button, go to New Query > From File > From Text, browse to the file, and press Import.
- Press Edit in the preview pane to open the Query Editor.
- Click the header of each column to convert. Ctrl+click selects several. Then go to Home > Transform > Data Type > Text.
- In the Change Column Type dialog, choose Replace Current.
- 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.