John SerraThese steps are for Excel on Windows. Don’t double-click the CSV file. Open a blank workbook in Excel, go to Data > From Text/CSV, pick the file, and click Transform Data instead of Load. In the Power Query editor, remove or replace any automatic type-conversion step, set your identifier columns to Text, and then choose Close & Load. The identifiers arrive as text, so leading zeros and long numbers are kept exactly as exported.
That sequence matters because a product code like 00123 is a five-character label, not the number 123. When Excel treats it as a number, a barcode scanner may reject the shortened code, shipping software may flag a four-digit postal code, and an account number that lost its last digit will not match anything.
Why Excel Changes Your Identifiers
When you double-click a CSV, Excel opens it and, under its default settings, converts text that looks like a number into a number. Recent Microsoft 365 builds may first show a notice that lists the conversions, such as “Remove leading zeros,” and offers Convert or Don’t Convert. Choosing Convert turns the identifier columns into numbers. Three things can go wrong:
- Leading zeros disappear. 00123 becomes 123, and the postal code 02138 becomes 2138.
- Long numbers lose digits. A worksheet number keeps 15 significant digits. An identifier with more than that, such as a 16-digit account ID, can lose its trailing digits when it is converted to a number. The account ID 1234567890123456 is stored as 1234567890123450 and displays as 1.23457E+15.
- Formatting does not bring them back. Setting a cell to Text after the fact doesn’t restore anything, because the digits were dropped while the file was being read. A custom format like
00000only pads the display to five digits. If the original code was 001234, the number is now 1234, the format shows 01234, and the real code is still gone.
The rule that follows is to decide the data type before the data reaches the grid.
Saving makes it worse. If you open a CSV directly and press Ctrl+S, Excel writes the converted values back into the file, and the original zeros and digits are gone from your source export. If you see stripped zeros, close the file without saving. Opening alone doesn’t change the file on disk. Work from a copy of the export and import it into a blank workbook.
The Example File
Here is an example of an export with three rows. To follow along, paste this text into a plain text editor and save it as sample-export.csv:
Row,Product_SKU,Postal_Code,Account_ID,Item_Name,Quantity,Unit_Price
1,00123,02138,1234567890123456,Precision Bolt,10,1.50
2,08492,07030,9876543210987654,Hex Nut,50,0.25
3,00045,00501,5555444433332222,Lock Washer,100,0.10
The leading Row column matters, because the checks below assume Product_SKU is in column B. Here is the same data as a table:
| Row | Product_SKU | Postal_Code | Account_ID | Item_Name | Quantity | Unit_Price |
|---|---|---|---|---|---|---|
| 1 | 00123 | 02138 | 1234567890123456 | Precision Bolt | 10 | 1.50 |
| 2 | 08492 | 07030 | 9876543210987654 | Hex Nut | 50 | 0.25 |
| 3 | 00045 | 00501 | 5555444433332222 | Lock Washer | 100 | 0.10 |
The three identifier columns are Product_SKU, Postal_Code, and Account_ID. Quantity and Unit_Price are real numbers and can stay numeric.
How to Import the CSV as Text
These steps are for Excel for Windows with the Get & Transform tools on the Data tab, which includes Microsoft 365, Excel 2021, and Excel 2024. Excel 2016 and 2019 also include Power Query on the Data tab. The labels below match Excel for Microsoft 365 on Windows (Version 2603). Menu names can differ slightly between builds, so match the idea if a label is a little different.
- Open a blank workbook. Launch Excel and start a new workbook. Don’t open the CSV from your file folder.
- Choose the file. On the Data tab, in the Get & Transform Data group, click From Text/CSV, select your CSV, and click Import. A preview window shows the detected delimiter, which should be a comma, and a grid of your data.
- Click Transform Data, not Load. When type detection is enabled, Load can apply the automatic number detection right away, which strips the zeros. Transform Data opens the Power Query editor, with the steps listed under Applied Steps on the right.
- Set the identifier columns to Text. Look at Applied Steps. If Power Query added a Changed Type step, it has probably already converted your identifier columns to whole numbers. Click the Product_SKU header, hold Ctrl and click Postal_Code and Account_ID, then on the Home tab open the Data Type dropdown and choose Text. If Excel asks whether to replace the current step or add a new one, choose Replace current. You can also delete the automatic Changed Type step first and then set the Text type. Adding a Text step after a number conversion does not bring the zeros back.
- Check before you load. The identifier columns should show the ABC icon, the values should be left-aligned, 00123 and 02138 should show their zeros, and Account_ID should show all 16 digits with no scientific notation.
- Close & Load. On the Home tab, click Close & Load. Excel returns the data to a worksheet as an Excel Table, with the identifier columns stored as text. To choose where it goes, use Close & Load To and pick a worksheet cell. Note where the table starts, because the checks below assume it starts at A1.
Prove It Worked
Looking at the screen is not enough, so test it. Put the checks outside the table, for example starting in cell J2, so Excel doesn’t try to copy the first formula down the table. Each one confirms that the cell is text and matches the expected string exactly. Assuming the table loaded at A1, with Product_SKU in column B, Postal_Code in C, and Account_ID in D:
| Check | Formula | Expected |
|---|---|---|
| Row 1 SKU | =AND(ISTEXT(B2), EXACT(B2, "00123")) |
TRUE |
| Row 1 postal code | =AND(ISTEXT(C2), EXACT(C2, "02138")) |
TRUE |
| Row 1 account ID | =AND(ISTEXT(D2), EXACT(D2, "1234567890123456")) |
TRUE |
| Row 2 SKU | =AND(ISTEXT(B3), EXACT(B3, "08492")) |
TRUE |
| Row 2 postal code | =AND(ISTEXT(C3), EXACT(C3, "07030")) |
TRUE |
| Row 2 account ID | =AND(ISTEXT(D3), EXACT(D3, "9876543210987654")) |
TRUE |
| Row 3 SKU | =AND(ISTEXT(B4), EXACT(B4, "00045")) |
TRUE |
| Row 3 postal code | =AND(ISTEXT(C4), EXACT(C4, "00501")) |
TRUE |
| Row 3 account ID | =AND(ISTEXT(D4), EXACT(D4, "5555444433332222")) |
TRUE |
For your own file, adapt the pattern: compare a few identifiers you know against the original text in the source system. Two quick spot checks also help: =LEN(B2)=5 confirms the character count, and =LEFT(B2,1)="0" confirms a zero survived.
When It Still Goes Wrong
| What you see | Why | What to do |
|---|---|---|
| You opened the CSV directly, then formatted the column as Text, and the zeros did not return. | The digits were dropped when Excel read the file. Formatting changes display, not the lost characters. | Close without saving. Open a blank workbook and import again with Data > From Text/CSV > Transform Data, setting the columns to Text. |
| The SKU shows 123 even after you set Text in Power Query. | An automatic Changed Type step earlier in the query already turned the column into whole numbers. | In Applied Steps, delete or replace that earlier step, then set Text. Check the raw file in a plain text editor if the value is still wrong. |
| A long ID ends in zeros. | Either Excel converted it to a number, or the export already contained the zeros. A trailing zero alone isn’t proof of damage. | Compare the cell with the original value in the source system. If the raw CSV already has the wrong digits, importing differently cannot recover them, so re-export from the source. |
| You want Excel to stop converting on its own. | The conversion setting applies when you open or paste data into the grid. It doesn’t control a Power Query import. | The Automatic Data Conversion settings exist in Microsoft 365 and Excel 2024. On Windows, go to File > Options > Data, look under Automatic Data Conversion, and clear “Remove leading zeros and convert to a number” and “Keep first 15 digits of long numbers and display in scientific notation.” On Microsoft 365 or Excel 2024 for Mac, go to Excel > Preferences > Edit and turn off the matching options. In earlier perpetual releases, such as Excel 2016, 2019, and 2021, use the controlled import steps above instead. |
| You reopen the workbook and a security warning says external data connections have been disabled. | Excel blocks the link back to the CSV until you allow it. | The imported text is saved in the workbook and stays intact. Choose Enable Content only if you trust the file and want to refresh from the CSV. |
| You have an older Excel and no From Text/CSV button. | Older builds can use the legacy wizard. | Enable it under File > Options > Data > Show legacy data import wizards, then use From Text (Legacy) and set the column format to Text in step 3. |
Frequently Asked Questions
Why does Excel limit numbers to 15 digits?
Excel stores worksheet numbers in a floating-point format that is accurate to about 15 significant digits. Digits beyond 15 can be lost when an identifier is converted to a worksheet number. Text isn’t stored that way, so a 16-digit identifier kept as text stays intact.
What is the difference between the 00000 format and Text?
A custom format like 00000 only changes how a number looks, by padding it to five digits. The cell still holds a number, so it can’t recover a code that was longer than five characters or that you need to match as text. Lookup formulas such as VLOOKUP also fail when one side is the number 123 and the other is the text “00123”. Storing the column as Text keeps the exact string.
If I save the imported table as a CSV, will the zeros survive?
The saved file will contain the text, including the zeros, as long as the cells are text. But a CSV carries no data-type information, so the next person who double-clicks it with default settings can see the same problem. Share the import steps with them, or send the data in a format that holds types.
Does Excel for Mac work the same way?
Microsoft documents Power Query import tools, including a From Text/CSV option, for Microsoft 365 for Mac, and the conversion preferences live under Excel > Preferences > Edit. The exact menus may differ from the Windows steps, so check the Data type options in the editor you see. This article’s steps were written for Windows. See Microsoft’s Power Query availability by Excel version for what each edition includes.
What to Do Next
Once your identifiers are safe, the next question is whether your rows mean what you think they mean. The Small Business Data Audit Checklist gives you a wider set of checks before you build a report on imported data.