Why Excel loses leading zeros
To Excel, a cell is either a number or text. A value made only of digits, such as 000123, looks like a number, so Excel stores it as the number 123. That is fine for prices and quantities, but many values that look like numbers are really codes: customer and employee IDs, ZIP codes, account numbers, phone numbers, SKUs and roll numbers. For a code, every character matters, and 000123 and 123 are different records.
The zeros usually vanish in one of three ways:
- Opening a CSV by double-clicking it. The zeros are in the file, but Excel converts each value as it opens it.
- Typing or pasting into a cell formatted as General. Excel converts the entry the moment you press Enter.
- Importing from another system that exported its codes as numbers in the first place.
There is a second, related problem. Excel keeps only 15 significant digits in a number, so a 16-digit card number or a long bank account number is silently changed: the last digits become zeros. Storing such values as text is the only way to keep them whole.
How to keep leading zeros in Excel manually
Format the column as Text before you enter data
Select the column, then choose Home › Number Format › Text (the drop-down in the Number group), or press Ctrl+1 and pick Text on the Number tab. Anything typed or pasted afterwards keeps its zeros. The order matters: changing the format after the zeros are gone does not bring them back, because the cell now holds the number 123.
Type an apostrophe first
Entering ’00123 tells Excel to store the value as text. The apostrophe is not shown in the cell and is not part of the value. This is handy for a single cell but slow for a whole list.
Use a custom number format
Press Ctrl+1, choose Custom and enter 000000 as the type. Excel then displays 123 as 000123. Be careful: this changes the display only. The value is still the number 123, so a lookup against a list of text codes will not find it, and other programs that read the file may see 123.
Rebuild the codes with TEXT
If the zeros are already gone and you know the length the codes should have, add a helper column with =TEXT(A2,"000000"), fill it down, then copy it and use Paste Special › Values over the original column. The result is text with the zeros restored.
Open CSV files safely
Instead of double-clicking a CSV, choose Data › From Text/CSV, select the file and click Transform Data. In Power Query, click the icon next to each code column’s header, choose Text, then Close & Load. With the legacy Text Import Wizard, set Column data format to Text for those columns in step 3.
Recent builds of Excel for Microsoft 365 also have File › Options › Data › Automatic data conversion. Clearing Remove leading zeros and convert to a number stops Excel dropping zeros from files you open afterwards.
Where this tool helps
Customer, employee and student IDs. Systems often issue fixed-width IDs such as 000123 or 0045. Once the zeros are gone, lookups and imports into other systems stop matching.
US ZIP codes. Many ZIP codes in the Northeast start with 0, such as 02139 in Cambridge, Massachusetts. Stored as numbers, they become four-digit values that mailing tools reject.
Phone numbers with a trunk prefix. Numbers written with a leading 0, such as 09876543210, lose that 0 and look one digit short.
Account numbers, SKUs and HSN codes. These need every digit for matching against bank statements, product catalogues and tax filings. Long account numbers also run into the 15-digit limit.
Files you pass to someone else. Sending an .xlsx with the codes already stored as text means the person opening it does not need to know any of the import steps above.
How the tool works
- Finding code columns. In automatic mode, a column counts as codes if any value starts with a zero, such as 00123, or if its header suggests a code (ID, ZIP, Postal, Account, SKU, Phone, Mobile, Employee, Roll, Aadhaar, PAN, GSTIN, HSN or Ref) and it holds mostly whole numbers.
- Storing as text. Whole numbers in those columns are written as text in the workbook. Decimals and values that are not numbers are left alone.
- Restoring lost zeros. By default, codes are kept as they are. You can pad each code to match the longest one in its column, or to a fixed number of digits, for example 5 for US ZIP codes.
- A clear summary. The tool reports which columns it changed and how many codes it padded, such as “Stored 3 numbers in Customer ID and PIN as text, so leading zeros stay.”
The file check on every SheetTidy tool also points here when it finds codes with leading zeros in a CSV, or whole numbers under a code header that are shorter than the longest code in a workbook, a sign that zeros were lost.
After the fix
If you need the codes back in a CSV for an upload, the Excel to CSV converter writes text cells exactly as stored, zeros included. Just remember that double-clicking that CSV in Excel will remove them again, so open it with Data › From Text/CSV or keep working from the .xlsx file.