How to Keep Phone Numbers and ZIP Codes Correct in Excel After a CSV Import

To keep phone numbers and ZIP codes correct after a CSV import, set those columns to Text during import — before Excel ever sees them as numbers. This is the single most reported CSV-to-Excel data loss: 0300 1234567 becomes 3001234567, ZIP 02139 becomes 2139, and a 16-digit card or account number becomes 4.12346E+15, corrupted beyond recovery. The damage happens at import, so the fix must happen at import too. (For the raw conversion step, the free CSV to Excel converter builds the workbook; the formatting below is what protects the values.)

Why Excel Eats These Values

Excel's default column type is General, which means "guess." Its guesses are optimized for math, not for identifiers:

• Leading zeros are dropped — 02139 → 2139. The zero was formatting, not value, in Excel's eyes.
• Long numbers become scientific notation — anything over 15 digits loses precision permanently. 4123456789012345 becomes 4123456789012350. The last digits are gone, not hidden.
• Plus signs vanish — +92 300 1234567 → 92 300 1234567 or a number, depending on the phase of the moon.
• Spaces and dashes may trigger date conversion — 01-02 becomes 1-Feb.

The 15-digit precision limit is the killer: it is lossy. Once Excel converts a 16-digit number to a Double, the original digits cannot be recovered from the workbook. Re-import is the only fix — which is why prevention beats repair.

Method 1: Set Text Type at Import (Prevention)

1. Data > From Text/CSV, select the file.
2. In the preview, click the phone/ZIP/code column header and change its Data Type to Text. Do this for every identifier column: phone, ZIP/postal, SKU, account numbers, ID numbers.
3. Load, save as .xlsx.

Text type stores exactly the characters in the file — zeros, plus signs, spaces, all preserved. The column cannot be used in arithmetic, which is correct: you never add two phone numbers.

For files you open by double-click (no import dialog), this method is unavailable — another reason to import explicitly. The same type discipline fixes scientific-notation corruption generally.

Method 2: Fix an Already-Imported File

If the file is already open and damaged:

• Leading zeros lost: the data is recoverable only if you know the expected length. For a 5-digit ZIP: =TEXT(A2, "00000") in a helper column rebuilds 02139 from 2139. For variable lengths, you need the source file — re-import properly.
• Scientific notation on 16+ digits: the trailing digits are gone. No formula recovers them. Re-import with Text type.
• Plus signs lost: retype or re-import; a helper formula cannot know where the + was.
• Turned into dates: 01-02 → 1-Feb is recoverable via re-import with Text type; the underlying serial number is not your data.

Rule of thumb: formatting damage (zeros, plus) can sometimes be rebuilt; precision damage (15+ digits) never can. When in doubt, re-import.

Formatting Phone Numbers Nicely (Without Breaking Them)

Once stored as Text, you can display them consistently without changing the stored value:

Better: normalize at import with Power Query (remove all non-digits, then format), so the workbook holds one canonical form. The column-mapping guide shows where to put that transform step.

Special Cases

International numbers with country codes. Keep the + and store as Text. If a system strips + on export, prepend it in Power Query ("+" & [Phone]) rather than by hand.

ZIP+4 and alphanumeric postcodes. 02139-1234 and SW1A 1AA must be Text — the dash/space makes number-guessing produce garbage or dates. UK/Canadian postcodes are never numbers; treat them as text always.

CSV round-trip. Saving the workbook back to CSV writes the displayed values. A Text column writes exactly what you see — safe. A Number-formatted column writes the number — leading zeros already lost. Keep the .xlsx as master.

Validation. After import, sanity-check with =LEN(A2) down a helper column: phone numbers should have consistent lengths, ZIPs consistent lengths. Outliers point at rows that need attention.

Validating Identifier Columns After Import

Prevention (Text type at import) plus verification (checks after) is the complete defense. After importing with Text types set:

1. Length check. In a helper column: =LEN(A2). Phone numbers from one source should share a length; ZIPs share a length. Filter the helper for outliers — a 9-digit phone in a column of 11-digit phones is either missing a digit or has an extra one.
2. Character check. Compare digit counts against total length to find letters hiding in numeric fields (in Power Query: Transform > Format > Trim, then check column quality statistics).
3. Uniqueness check. =COUNTIF($A$2:$A$1000,A2)>1 flags duplicated identifiers — paired with the duplicates guide for the full workflow.
4. Pattern check. For fixed-format codes, Data > Data Validation > Custom with a formula like =AND(LEN(A2)=5, ISNUMBER(--A2)) stops future bad entries at the door.

Run these checks once per new source, then bake the good ones into the import query (Power Query custom columns) so every refresh self-validates. A five-minute check on the first file prevents a five-hour forensic dig six months later when someone notices the ZIPs were wrong all along.

Document the expected format. A one-line note per identifier column — "Phone: 11 digits, leading 0, Text" / "ZIP: 5 digits, Text" — stored on a README sheet or in the import query's description. The next person (or you in a year) will not have to reverse-engineer which columns are fragile. Fragile columns deserve documentation; robust ones do not need it.

FAQ

Why does Excel remove the zero from the start of my numbers?

Because General format treats the value as a number, and numbers don't have leading zeros. Set the column to Text at import — before Excel converts it.

Can I recover a 16-digit number that became 4.12E+15?

No. Excel stores numbers with ~15 digits of precision; the rest were discarded at import. Re-import the CSV with the column set to Text.

Should phone numbers be stored as Text or as a custom number format?

Text. A custom number format (like 0000000000) displays zeros but the underlying value is still a number — it breaks the moment the value exceeds 15 digits or someone copies values elsewhere. Text stores what you see.

How do I stop Excel converting 01-02 to a date?

Import with the column set to Text. If it's already converted, re-import — the date serial replaced your text and can't be reversed reliably.

Do these rules apply to Google Sheets too?

Yes — Sheets also guesses types on CSV import (File > Import > select "Detect automatically" pitfalls). The prevention is the same: specify column types, or pre-format. The Google Sheets export workflow touches related import hygiene.

Final Checklist

• Identifier columns (phone, ZIP, SKU, account/ID numbers) set to Text at import — verified in the preview.
• No 16+ digit number ever passed through General format.
• Length spot-check (LEN) run on phone/ZIP columns; outliers investigated.
• Display formatting done with formulas on Text values — underlying data untouched.
• Master saved as .xlsx; CSV treated as export-only.

If all five checks pass, every phone number and ZIP code in your workbook is exactly as the source wrote it — zeros, plus signs, and all.