As a guy who uses Excel to open CSV (comma-separated values) files pretty often, I’d like to offer a pro tip:
Excel will always drop leading zeros in a column of data unless you *import* the CSV file!
In my Day Job, I often have to work with CSV files which contain job codes which sometimes start with zero — like “08124500” and similar. If I just double-click that file and open it in Excel, well, those leading zeros will always go bye-bye.
If that’s not what I need (and usually it isn’t), then the only way around it is to open Excel on its own, and then import the CSV file the, uh, slow way.
(I’m using Excel 2016 currently, but instructions here will be similar for most versions from 2010 and later.)
- Open Excel; i.e., a new blank Excel file.
- In the Office ribbon, choose the DATA tab.
- Look for a section of the ribbon named “Get External Data.” Click the “From Text” button.
- A file selection window titled “Import Text File” appears. Navigate that to your CSV file. Click IMPORT.
- Excel will walk you through the multi-step file import. The important thing here is Step 3. Click the column with the leading zeros, and set its Data Format as TEXT, rather than GENERAL.
- When Excel imports your CSV data, the leading zeros will still be in place!
So remember: When bringing CSV data into Excel, you have to account for the fact that Excel will always try to figure out what type of data you have — and to Excel, “08253641” sure looks like a number. Which means the leading zero will be dropped. And if Excel guesses wrong, you might have big problems!
