Excel Leading Zeros: How to Keep Them in Codes and CSV Files
Excel removes leading zeros because it treats anything that looks like a number as a number, and a zero at the front has no mathematical value. That’s fine for prices, but it breaks values that are really codes, such as ZIP codes, barcodes, SKUs and product IDs. A barcode like 012345678905 quietly becomes 12345678905.
The fix depends on where you are. You can stop Excel before you type, add the zeros back after they’re gone, or open a CSV file without losing them in the first place.
How to Keep Leading Zeros in Excel
The most reliable way to keep leading zeros is to store the value as text, so Excel stops treating it as a number. Each method below does this differently, and the right one depends on whether you’re typing new data, fixing a column or preparing a file for another system.
| Method | What Excel stores | Best for |
|---|---|---|
| Format cells as Text | Text | Columns of codes you’re about to type or paste |
| Type an apostrophe first | Text | A few individual entries |
| Custom number format | A number, with zeros shown only on screen | Fixed-length numbers you want to keep numeric |
| Turn off automatic conversion | Text, for values with leading zeros | Stopping Excel from removing zeros everywhere |
Format cells as Text before entering data
Formatting a column as Text tells Excel to keep exactly what you type. Select the cells, press Ctrl+1, choose Text on the Number tab and click OK. You can also pick Text from the number format list on the Home tab.
Do this before entering or pasting the data. Changing the format afterward doesn’t bring back zeros Excel has already removed, so a column of barcodes needs the Text format first and the values second.
Type an apostrophe before the number
For a one-off entry, type an apostrophe before the value, such as '02134. Excel stores the entry as text and keeps the zero, and the apostrophe itself doesn’t appear in the cell.
Excel may show a small green triangle in the corner of the cell, warning that a number is stored as text. For codes, that’s exactly what you want, so you can ignore it. Avoid choosing Convert to Number from that warning, because it removes the zeros.
Use a custom number format
A custom number format pads numbers with zeros on screen while keeping them numeric. Select the cells, press Ctrl+1, choose Custom and enter a code with one zero per digit, such as 00000 for five-digit ZIP codes. The Special category also includes ready-made ZIP Code and ZIP Code + 4 formats.
This method has two limits. It pads every value to the same length, so it doesn’t suit codes of different lengths, and it only changes how the value looks, while the cell still holds the plain number. Custom formats also work only for codes with fewer than 16 digits.
Because the stored value has no zeros, a custom format is risky for data you’ll export or import elsewhere. If the codes need to leave Excel intact, store them as text with one of the other methods.
Turn off automatic removal of leading zeros
Excel for Microsoft 365 and Excel 2024 let you switch off this behavior entirely. On Windows, go to File > Options > Data, and on a Mac go to Excel > Preferences > Edit. Under Automatic Data Conversion, clear Remove leading zeros and convert to number.
With that option off, values such as 00123 stay as text whether you type them, paste them or open them from a CSV or text file. The same section can also stop Excel from shortening long numbers to scientific notation and from turning entries like JAN1 into dates. Older versions of Excel don’t have these settings, so use the Text format or the import method below instead.
How to Add Back Leading Zeros Excel Removed
If the zeros are already gone, you can rebuild them with a formula, as long as you know how long each code should be. The TEXT function pads a number to a set length and returns text. For example, =TEXT(A2,"00000") turns 2134 into 02134.
The RIGHT function gives the same result in a different way. =RIGHT("00000"&A2,5) adds zeros to the front, then keeps the last five characters. Both formulas suit fixed-length codes such as US ZIP codes, which have five digits, or UPC-A barcodes, which have 12.
| Code type | Value after Excel removed zeros | Formula | Result |
|---|---|---|---|
| ZIP code (5 digits) | 2134 | =TEXT(A2,"00000") |
02134 |
| UPC-A barcode (12 digits) | 12345678905 | =TEXT(A2,"000000000000") |
012345678905 |
Put the formula in an empty column next to your data and fill it down. When the results look right, copy the column and use Paste Special > Values to replace the original codes, then delete the helper column. This leaves real text values rather than formulas that depend on the old column.
Formulas can’t help with codes of varying length. If your SKUs range from 4 to 8 characters, Excel has no way of knowing how many zeros each one originally had. In that case, go back to the source file or system and bring the data in again using one of the methods that keeps zeros.
Keep Leading Zeros When Opening and Saving CSV Files
CSV files are where leading zeros most often disappear, because double-clicking a CSV lets Excel convert every column automatically. That’s a common problem with product exports and import files, where barcodes, SKUs and ZIP codes all need to survive the trip.
To open a CSV safely, import it instead of opening it directly:
- Open a blank workbook and go to Data > From Text/CSV.
- Select the file and click Import.
- In the preview, choose Transform Data (labeled Edit in some versions) to open the Power Query Editor.
- Select each column that holds codes, using Ctrl+click for several at once, then set Data Type to Text and choose Replace Current.
- Click Close & Load to bring the data into the sheet with its zeros intact.
Power Query imports aren’t affected by the automatic conversion settings, so this method works the same way in every recent version. If you regularly receive files with the same layout, you can refresh the query with Data > Refresh instead of repeating the steps.
Saving needs equal care. When the file is ready, save it as CSV UTF-8, and avoid reopening that CSV by double-clicking it and saving again, because Excel can strip the zeros on the way back in. To check the result, open the CSV in a plain text editor and confirm each code still starts with its zeros.
Watch for long numbers too
Long codes have a related problem. Excel keeps a maximum of 15 significant digits in a number, so digits beyond the 15th turn into zeros, and long values may display in scientific notation, such as 1.23E+15. A 16-digit code typed into a General-formatted cell can’t be recovered once Excel changes it.
The fixes are the same as for leading zeros. Format the column as Text before entering long codes, type an apostrophe first, or import them as Text through Power Query. In Excel for Microsoft 365 and Excel 2024, you can also turn off the automatic conversion option that shortens long numbers.
Conclusion
Excel leading zeros disappear because Excel assumes every number-like value is a number, so the lasting fix is to store codes as text from the start. Whether you format columns as Text, import CSV files through Power Query or switch off automatic conversion, your ZIP codes, barcodes and SKUs will keep their real values. We hope this guide helps you keep leading zeros wherever your data goes next. If you prepare product data in spreadsheets for store imports, you can explore Bulkflow.
