How to open a US City List CSV in Excel or Google Sheets without losing the leading zeros

Which columns are at risk?

Any column that holds a FIPS code or a ZIP code, and in the files that carry them, the geohash_* columns. They are codes, not quantities, so they should stay text.

If your spreadsheet reads these columns as numbers, it deletes the first zero, and every row of seven states (Alabama, Alaska, Arizona, Arkansas, California, Colorado and Connecticut) ends up with a code that matches nothing. The rows are still there, and nothing shows an error.

The reason is short. A county code is the 2-digit state code followed by 3 digits, so a state numbered 01 to 09 puts a zero at the front of every one of its codes. California is 06.

File	Column	Rows that start with a zero	Which rows
City lists	fips_place_code	4,505 of 48,490	Every row of AL, AK, AZ, AR, CA, CO and CT. California alone is 1,618 rows.
City lists	county_fips	4,505 of 48,490	The same rows
County lists	county_fips	318 of 3,221	Every county in the same seven states
ZIP Code List with Coordinates	zip_code	2,580 of 33,799	Every row in our ZIP file for CT, MA, ME, NH, NJ, RI, VT, Puerto Rico and the US Virgin Islands, plus one New York ZIP (06390)

Three real rows from the files show what the damage looks like:

• Abanda, Alabama (the first row of the city file): fips_place_code 0100100 becomes 100100, and county_fips 01017 becomes 1017.
• Autauga County, Alabama: 01001 becomes 1001.
• Adjuntas, Puerto Rico: the ZIP 00601 becomes 601.

A second, smaller hazard: the geohash_* columns. A geohash is letters and digits, and in Hawaii some of them look like scientific notation to a spreadsheet: 8e3 is read as 8,000. In the files that carry geohash columns, this affects 81 places in the City lists, 2 counties in the County lists and 39 ZIPs, all in Hawaii. The fix is the same one (import as text), and Excel's own setting covers it too (Option 2 under Excel).

One more line for the city files that carry a zip_codes column: it holds a single ZIP for some places, and 1,759 of those begin with a zero. Import that column as text as well.

How do I open the CSV in Excel without losing the zeros?

Import it with data types turned off, so every column arrives as text. Excel has two ways to do it. Option 1 works in every version that has Data → From Text/CSV; Option 2 is a one-time setting that only newer versions have.

Option 1 (recommended): import the file and keep every column as text

1. Unzip the download. The CSV is inside, next to column-definition.md and methodology.md.
2. Open a blank workbook, then go to Data → From Text/CSV and pick the CSV.
3. In the preview window, set Data Type Detection to Do not detect data types, then choose Load. Every column arrives as text, codes included.
4. If you need a numeric column as a number (for example population, or households in How-to 1), convert just that column afterwards.

An alternative, if you want to keep type detection on for the other columns: in the preview, choose Transform Data, select the code columns, set Data Type → Text, then Close & Load.

Option 2: change Excel's own setting once

Excel for Microsoft 365 and Excel 2024 have an Automatic Data Conversion section in their settings. On Windows, it is under File → Options → Data. On a Mac, it is under Excel → Preferences → Edit. Turn off Remove leading zeros and convert to number. For the geohash case, also turn off Convert digits surrounding the letter "E" into a number in scientific notation. After that, double-clicking a CSV keeps the zeros.

The source is Microsoft's own page, Set automatic data conversions. Older versions of Excel do not have this section, so Option 1 is the one that works everywhere.

How do I open the CSV in Google Sheets without losing the zeros?

Import it, and turn off the conversion of text to numbers.

1. Go to File → Import → Upload, and pick the CSV.
2. Turn off "Convert text to numbers, dates, and formulas."
3. Choose Import data.

How do I check that the zeros survived?

Compare your sheet with these numbers. Opened correctly, the free Basic City List shows:

Check	Expected
Rows (not counting the header)	48,490
Columns	10
Row 2 fips_place_code	0100100 (7 characters)
Row 2 county_fips	01017
=COUNTIF(B:B,"0*") on fips_place_code	4,505

The free Basic County List has 3,221 rows and 9 columns, and its county_fips shows 318 codes that start with a zero (county_fips is column F, so use =COUNTIF(F:F,"0*")).

The last check works because the wildcard 0* only matches text. If the result is 0, the column arrived as numbers, and you should import again.

The quick version: select the column and look at the cells. Text is left-aligned. Right-aligned cells are numbers.

I already opened the file and lost the zeros. Can I repair it?

Yes, without downloading it again, as long as the only damage is the missing zero. Put one of these formulas in an empty column next to the damaged one. A2 stands for the first damaged cell, so change it to match. These were tested against every row of the shipped files, and they rebuild every code correctly.

• county_fips (5 characters): =TEXT(A2,"00000")
• zip_code (5 characters): =TEXT(A2,"00000")
• fips_place_code (7 characters for cities and census places, 10 for townships):

  =IF(LEN(A2)<=7, TEXT(A2,"0000000"), TEXT(A2,"0000000000"))

The third formula needs two lengths because some codes are 10 characters long; 149 of the 4,505 affected rows are townships. A single 7-zero pad would turn a damaged township code into the wrong code, with no error to tell you.

When the new column looks right, copy it and paste it back as values, then delete the damaged column.

Get the file

The free Basic City List (/lists/cities/basic) and the free Basic County List (/lists/counties/basic) are both free downloads with no account needed. Each has a free 100-row sample (city, county), which is a quick way to try the steps above before opening the full file.