How do I export and save data with fields without losing leading zeros (e.g. on phone numbers)
Opening CSV files in Excel that have leading zeros in columns causes Excel to read those columns as numbers and silently drop the zeros. For example, “0875” becomes “875”. Note that this is not a formatting issue, and converting the column to Text won’t cause the leading zero to reappear – the data is permanently changed (by Excel) and becomes an issue if the file is then re-saved.
Export and save the file ready to import
- Save the file to your computer.
- Never open the file in Excel directly, as opening in Excel is the process that removes the leading zeros.
- Ensure the file is saved as a CSV
- If you wish to ensure that your file still contains leading zeros:
- In your file directory, right click and open the file with notepad.
- Check for an example number that should contain a leading xero (use Ctrl+F).
Export the file then edit the content in Excel
- Save the file to your computer's desktop. Do not open the file at this point.
- Open a blank Excel Spreadsheet.
- In Excel 2010, open the Data tab of the ribbon. In the Get External Data section, click the From Text icon.
- Select the file you saved to your desktop and press Import.
- In Step 1 of the Wizard, choose the file type Delimited.
- In Step 2 of the Wizard, select Comma as the delimiter, and untick all other options.
- In Step 3 of the Wizard, click on the column(s) that should retain the zeros (so it's coloured black) and change its type to Text in the Column Data Format section.
- Press Finish.
Format the number columns as text
If the number fields still do not have the zero in front, at this stage converting the cells to text will now work.
- Select all of the cells in the column.
- Right click in the highlighted area and select Format Cells...
- Choose text.
- Press OK.