How to Keep Leading Zeros in Excel 365 CSV Files
Question details
Users need to maintain leading zeros in number fields when exporting data to CSV files for third-party system imports.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Exporting inventory or ID data requiring a specific digit length (e.g., a 6-digit code like 000000) to a CSV file for import into an inventory system.
- Observed behavior
- Excel drops leading zeros in CSV exports even when a custom number format is applied, reverting values like '090' back to '90'.
Before exporting your file, note that CSV (Comma Separated Values) is a plain text format. Standard Excel custom number formats only change how data looks on your screen, not the underlying value written to the exported text file.
Use the TEXT Formula to Convert Numbers to Text
This is the most reliable method to force Excel to write the leading zeros permanently into the CSV data before exporting.
By converting the number into a definitive text string using a formula, Excel is forced to export the characters exactly as they appear, preserving all leading zeros for third-party imports.
Insert a new blank column right next to the data that requires leading zeros.
In the new column, type the formula =TEXT(A1,"000") (assuming your data is in A1). Replace "000" with your required number of digits, such as "000000" for a six-digit inventory code.
Drag the fill handle down to apply the formula to all rows. Then, select the new column, press Ctrl+C to copy, right-click the original column, and select Paste Special > Values.
Delete the helper column. Go to File > Save As and choose CSV (Comma delimited). Open the resulting file in Notepad to verify the zeros are preserved.
Manually Format Cells as Text Before Typing
Best for small datasets or manual data entry where you need to type the leading zeros yourself.
Handle Data Exports and Leading Zeros with WPS Spreadsheet
WPS Spreadsheet offers precise control over cell formatting and text values, making it simple to prepare, format, and export CSV files without losing critical data like leading zeros.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the dataset you need to prepare for export.
- 2. Apply text conversion: Use the =TEXT(A1, "000000") function to pad your numbers with the required amount of zeros.
- 3. Paste as values: Copy the formula results, right-click the destination column, and choose 'Paste as Values'.
- 4. Export to CSV: Click Menu > Save As, choose 'CSV (Comma delimited)' from the file format dropdown, and save your file safely.
Frequently Asked Questions
Why does Excel remove leading zeros even when I set a custom format?
Custom formats (like '000000') only change how the data is visually displayed on the screen. The underlying value remains a standard number. When saving to a plain text format like CSV, Excel writes the underlying numeric value, which naturally drops any leading zeros.
How can I accurately check if my CSV file kept the leading zeros?
Do not double-click the CSV file to check it, as Excel will reopen it and strip the zeros again. Instead, right-click the CSV file, select 'Open with', and choose 'Notepad'. What you see in Notepad is exactly what the third-party program will see when importing.
Why did this format export perfectly in Excel 2016 but not in Excel 365?
Microsoft frequently updates how Excel 365 handles background data types and text-to-column conversions, often becoming more aggressive at automatically converting perceived numbers into standard numeric values. Using the TEXT function is a foolproof workaround across all versions.
Is there a way to open a CSV in Excel without losing existing leading zeros?
Yes. Instead of double-clicking the file, open a blank Excel workbook. Go to the Data tab, select 'From Text/CSV', select your file, and use the Power Query editor (Transform Data) to set the specific column's data type to 'Text' before loading it into the spreadsheet.
