How to Clean Phone Numbers in a CSV (Format, Dedupe, Remove Invalid)

To clean phone numbers in a CSV: back up the file, decide which country to assume for numbers without a country code, convert every number to E.164, drop the invalid ones, then remove duplicates on the E.164 value. Doing it in that order matters. Deduplicating before formatting misses most duplicates, and formatting without a default country turns local numbers into the wrong country's numbers.

Here's the process step by step, then how to run it on a whole file in one go.

Step 1: Keep the original

Never overwrite the raw phone column. Add new columns next to it. You'll want the original to fix typos by hand, to explain odd results, and to undo a wrong country guess.

Step 2: Work out the default country

A number without a + and country code only makes sense in one country. 020 7946 0958 is London if the list is British and nonsense if it's American. Before converting, decide:

• Single-country list (e.g. a US CRM export): assume that country for every number without a code.
• Mixed list with a country column: use the row's country.
• Mixed list without one: convert only numbers that already start with + or 00, and review the rest. Guessing produces numbers that are valid somewhere else, which is worse than flagging them.

Step 3: Convert to E.164

E.164 is +, country code, number, no punctuation, for example +14155552671. It's the format SMS APIs, dialers and most CRMs want, and the only one that makes duplicates comparable. The rules, including the trunk zero that has to go in most countries but stay in Italy, are in E.164 format explained.

Use a numbering-plan library for this, not find-and-replace. Each of these inputs, read as US numbers, comes out the same:

Raw	E.164
(415) 555-2671	+14155552671
415.555.2671	+14155552671
1-415-555-2671	+14155552671
+1 415 555 2671	+14155552671

Step 4: Separate the invalid numbers

A number is invalid if its length or prefix doesn't exist in its country's plan. Typical causes in real lists:

• digits missing or doubled (415-555-267, +1 415 555 26711);
• placeholders such as 000-000-0000, 1234567890 or n/a;
• an extension glued on (2125550100x204), which belongs in its own column;
• two numbers in one cell (415-555-2671 / 415-555-0123);
• a local number with the wrong default country.

Move invalid rows to a separate file or flag them. Don't delete the whole row: the email or name may still be good, and some typos are obvious to a human.

Step 5: Remove duplicates on E.164

Now an exact match on the E.164 column catches every duplicate, however it was originally written. Decide which row wins (usually the most recently updated, or the one with the most fields filled in) before you delete anything.

Step 6: Tag line type and country

Two extra columns pay for themselves:

• Country, for time zones and local calling rules.
• Line type (mobile, landline, toll-free, VoIP), so texts only go to mobiles and callers know what to expect. In the US and Canada the numbering plan can't tell mobile from landline. See mobile vs landline lookup.

Step 7: Import without Excel breaking it

Excel will strip the +, drop leading zeros and turn long numbers into 1.41556E+10 if it opens the CSV directly. To avoid it:

• import via Data → From Text/CSV and set the phone column to Text;
• or upload the CSV straight into your CRM or dialer without opening it in Excel;
• or keep the file in Google Sheets with the column formatted as plain text. See validating phone numbers in Google Sheets.

Doing all of it in one upload

In SimpleVerifier, steps 3 to 6 happen in one pass. Upload a CSV on the verifier with a column named phone, mobile, cell, direct phone or similar (we also spot an unnamed column that's mostly phone numbers). Every row gets four new columns:

Column	Values
phone_status	valid, invalid or empty
phone_e164	e.g. +14155552671
phone_country	ISO code, e.g. US, GB
phone_type	mobile, landline, mobile_or_landline, toll_free, voip…

Then download valid phones only, or the whole file with the new columns, and dedupe on phone_e164. Numbers without a country code are read as US numbers, so for other countries add the country code first.

If the same file has emails, each address also gets a live mailbox check in the same upload. Phone format validation is included in the $29.99 plan with no per-number charge. To spot duplicates, typos and dead domains on the email side before paying, use the free list health check.

Related: how to check if a phone number is valid · remove invalid emails from a CSV