Kayabyte

How do you keep leading zeros when you open a CSV in Excel on a Mac?

By Kayabyte · Last updated 4 November 2026

If a ZIP code such as 02134 or an ID such as 00123 turns into 2134 or 123 when you open a CSV in Excel, the zeros were lost when Excel read the column as numbers. Do not double-click the file. Import it instead and set that column to Text before it loads: Microsoft’s own page on importing text and CSV files says you may need the import wizard to “convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros” (support.microsoft.com, checked 4 November 2026). The CSV file itself still has the zeros; the loss happens on opening, and a file saved from Excel afterwards keeps the loss.

Why the zeros disappear

A CSV is plain text with no column types. When you double-click it, Excel decides for each column what it holds. A column of digits becomes numbers, and a number has no leading zeros. The same guess can turn long codes into scientific notation and some codes into dates. Microsoft’s page says Excel applies its default data format settings to each column when it opens a .csv file.

Method 1: import and set the column to Text

  1. Open Excel first, with a blank workbook.
  2. Use the import route for text files. In current Excel this is on the Data tab (Get Data, From Text/CSV); older versions start a Text Import Wizard. Menu names vary by Excel version, so look for a “from text” or “import” entry.
  3. In the preview, set the column that holds the codes to Text, not General.
  4. Load the data and check a few codes against the original file.

Microsoft’s page also notes that if you rename a .csv file to .txt, Excel starts the Import Text Wizard when you open it. That is a quick way to get the column-by-column choices.

Method 2: fix a column that already lost its zeros

If the codes are already numbers and all have the same length (for example 5-digit ZIP codes), a formula can put the zeros back: =TEXT(A2,"00000") for five digits. Copy the result and Paste Special, Values over the original. This only works when every code should have the same length. For mixed-length codes, go back to the CSV and import it again with the column set to Text.

What to avoid

With Gridhand

Gridhand Convert reads CSV, TSV and text files with the encoding (BOM, UTF-8, UTF-16, Windows-1252) and delimiter (comma, semicolon, tab, pipe) detected, and keeps IDs with leading zeros as text. You can drop many CSVs in at once and get one .xlsx per file, with the preview showing the columns before anything is written. Originals are never modified. The same applies in Clean: codes with leading zeros, and codes of 12 or more digits, stay text instead of being turned into numbers.

This page does not cover what a particular version of Excel does with the resulting .xlsx files beyond opening them; check a few codes after opening.

Steps with Gridhand

  1. Drop the CSV files, or a folder of them, into Gridhand and choose Convert, “CSV, TSV or text to XLSX”.
  2. Check the encoding and delimiter in the preview, and look at your code column.
  3. Choose an output folder and press Run.
  4. Open the .xlsx in Excel and compare a few codes with the CSV.

Free for up to 3 files per run; Gridhand Pro is USD 49 once. Download Gridhand · Pricing

Questions

Are the zeros really gone from my CSV file? Not as long as you only opened it and did not save over it. Open the file in TextEdit to see the raw text.

Why does a number like 12345678901234 show as 1.23457E+13? Excel read the column as numbers and shows long ones in scientific notation. Import the column as Text to keep every digit.

Will Gridhand turn my IDs into numbers? No. Codes with leading zeros stay text, and so do codes of 12 or more digits.

Microsoft and Excel are trademarks of the Microsoft group of companies. Gridhand is made by Kayabyte and is not affiliated with or endorsed by Microsoft.