How to keep leading zeros when you export a table to CSV or Excel

You copy a table, open it in Excel, and ZIP code 02134 is now 2134. Excel treats anything that looks like a number as a number, and numbers have no leading zeros. The CSV file is usually fine; Excel changes the value when it opens it.

1. Import the column as Text

In Excel choose Data, then From Text/CSV, then Transform Data (or Text Import Wizard in older versions). Select the column and set its type to Text before loading. The zeros stay.

2. Format the column first, then paste

Select the empty column in a new sheet, choose Format Cells, then Text. Paste the copied table after that. Pasting into cells that are already Text keeps 02134 as typed.

3. Fix it after the fact

If the zeros are already gone, use a custom number format. For 5-digit ZIP codes choose Format Cells, Custom, and type 00000. For 10-digit phone numbers type 0000000000. This only changes how the number looks, so it works when every value has the same length.

Google Sheets

Select the column, then Format, Number, Plain text, before you paste. Sheets drops zeros the same way otherwise.

Check the source

Open the CSV in a plain text editor. If the zeros are in the file, the export was fine and only the spreadsheet view needs fixing. Table to CSV & Excel – Table Copier copies cell text exactly as shown on the page, so the file keeps 02134.

Related: How to export a table from Chrome to Excel and How to export an HTML table to a CSV file.