Normalizing US phone numbers to E.164 in Python and Excel before a CRM import

This post was written with AI assistance and reviewed before publishing. All phone numbers below use the 555-0100 to 555-0199 range, which is reserved for fictional use.

Why normalize before the import

Phone columns in contact lists arrive in every shape: (201) 555-0101, 201.555.0102, 1-201-555-0103, 201-555-0105 x12. Most CRMs match, deduplicate and dial more reliably when every number uses one format, and E.164 is the usual choice: a plus sign, the country code and the national number with no spaces, so +12015550101.

If you normalize inside the CRM after the import, you often end up with duplicate contacts and numbers that were silently rejected. Doing it in the file first means you can see every row that did not convert and decide what to do with it.

This post covers three ways to do it:

1. Python with the phonenumbers library (recommended)
2. A no-dependency Python fallback for US numbers
3. An Excel formula for when the list never leaves a spreadsheet

The rules we want

• Output +1 plus 10 digits for valid US numbers
• Keep extensions in their own column, never glued to the number
• Never overwrite the original value: add new columns next to it
• Give every row a status (ok, blank, invalid and so on) so nothing disappears quietly

Option 1: Python with phonenumbers

phonenumbers is the Python port of Google's libphonenumber. It knows country codes, valid number ranges and extension formats.

pip install phonenumbers pandas 

import re import phonenumbers from phonenumbers import NumberParseException, PhoneNumberFormat # Values like "2.01556E+09" mean a spreadsheet already damaged the number. SCI_NOTATION = re.compile(r"^\s*\d+(\.\d+)?[eE]\+?\d+\s*$") def normalize_phone(raw, default_region="US"): """Return (e164, extension, status) for one raw phone value.""" if raw is None: return None, None, "blank" text = str(raw).strip() if not text: return None, None, "blank" if SCI_NOTATION.match(text): return None, None, "scientific_notation" try: number = phonenumbers.parse(text, default_region) except NumberParseException: return None, None, "unparseable" if not phonenumbers.is_valid_number(number): return None, None, "invalid" e164 = phonenumbers.format_number(number, PhoneNumberFormat.E164) return e164, number.extension, "ok" 

default_region="US" tells the parser how to read numbers written without a country code. Numbers that start with + keep their own country code, so +44 20 7946 0958 stays a UK number instead of being forced into +1.

Applying it to a CSV with pandas

import pandas as pd # dtype=str keeps "2015550101" from becoming 2015550101.0 # keep_default_na=False keeps empty cells as "" instead of NaN df = pd.read_csv("contacts.csv", dtype=str, keep_default_na=False) results = df["phone"].map(normalize_phone) df["phone_e164"] = results.map(lambda r: r[0]) df["phone_ext"] = results.map(lambda r: r[1]) df["phone_status"] = results.map(lambda r: r[2]) print(df["phone_status"].value_counts()) df[df["phone_status"] != "ok"].to_csv("phones_to_review.csv", index=False) df.to_csv("contacts_normalized.csv", index=False) 

The original phone column is untouched. The review file holds every row that did not convert, so someone can fix the source instead of guessing.

is_valid_number versus is_possible_number

is_possible_number only checks length. is_valid_number also checks the number against known ranges, which is stricter but depends on the library's metadata. Keep phonenumbers updated so newly assigned area codes are recognized, and look at the invalid rows before deleting anything.

Option 2: a no-dependency fallback for US numbers

If you cannot install packages (a locked-down server, a quick script on a colleague's laptop), this function does a format check for North American Numbering Plan numbers using only the standard library.

import re SCI_NOTATION = re.compile(r"^\s*\d+(\.\d+)?[eE]\+?\d+\s*$") EXT_PATTERN = re.compile(r"\s*(?:ext\.?|extension|x|#)\s*(\d{1,6})\s*$", re.IGNORECASE) def normalize_us_phone(raw): """Format check for US (NANP) numbers. Return (e164, extension, status).""" if raw is None: return None, None, "blank" text = str(raw).strip() if not text: return None, None, "blank" if SCI_NOTATION.match(text): return None, None, "scientific_notation" extension = None match = EXT_PATTERN.search(text) if match: extension = match.group(1) text = text[: match.start()] digits = re.sub(r"\D", "", text) if len(digits) == 11 and digits.startswith("1"): digits = digits[1:] if len(digits) != 10: return None, extension, "wrong_length" # Area codes and exchange codes cannot start with 0 or 1.  if digits[0] in "01" or digits[3] in "01": return None, extension, "not_nanp_format" return "+1" + digits, extension, "ok" 

What it returns for some typical inputs:

Limits to know about:

• It checks format, not whether the number is assigned. phonenumbers is stricter.
• +1 covers the whole North American Numbering Plan, which includes Canada and several Caribbean countries, so +1 alone does not prove a number is in the US.
• International numbers come back as wrong_length, which is the right outcome for a US-only list but the wrong one for a mixed list. Use phonenumbers for mixed lists.

Option 3: Excel

Step 1: Open the file with the phone column as Text

Do not double-click the CSV. In Excel, use Data > From Text/CSV, choose Transform Data, select the phone column and set its type to Text, then load. In older versions, the Text Import Wizard has a Column data format: Text option on its last step.

This stops Excel from turning long digit strings into numbers, which is where scientific notation and lost leading characters come from.

Step 2: Add a helper column (Excel 365 and Excel 2021 or later)

With the raw phone in column A, put this in B2 and fill down:

=IF(TRIM(A2)="","",LET( raw, TRIM(A2), low, LOWER(raw), cut, MIN(IFERROR(SEARCH("x",low),LEN(low)+1), IFERROR(SEARCH("#",low),LEN(low)+1)), base, LEFT(raw,cut-1), chars, MID(base,SEQUENCE(LEN(base)),1), digits, TEXTJOIN("",TRUE,IF(ISNUMBER(--chars),chars,"")), ten, IF(AND(LEN(digits)=11,LEFT(digits,1)="1"),RIGHT(digits,10),digits), IF(AND(LEN(ten)=10,LEFT(ten,1)>="2",MID(ten,4,1)>="2"),"+1"&ten,"CHECK") )) 

How it works:

• cut finds the first x (which also catches ext and extension) or #, and everything from there on is treated as the extension
• chars splits the remaining text into single characters, and digits keeps only the ones that are numbers
• ten drops a leading 1 country code
• The last line applies the same area code and exchange check as the Python fallback and returns CHECK for anything that fails

To pull the extension into its own column (C2):

=IF(TRIM(A2)="","",LET( low, LOWER(TRIM(A2)), cut, MIN(IFERROR(SEARCH("x",low),LEN(low)+1), IFERROR(SEARCH("#",low),LEN(low)+1)), tail, MID(low,cut,LEN(low)), IF(tail="","",LET( chars, MID(tail,SEQUENCE(LEN(tail)),1), TEXTJOIN("",TRUE,IF(ISNUMBER(--chars),chars,"")) )) )) 

It uses the same cut position and keeps only the digits after it, so 201-555-0105 ext. 7 gives 7 and a number with no extension gives an empty cell.

Step 2 for older Excel versions

Without LET and SEQUENCE, strip the common punctuation with nested SUBSTITUTE calls instead:

B2: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",""),"-",""),"(",""),")",""),".",""),"+","") C2: =IF(AND(LEN(B2)=11,LEFT(B2,1)="1"),RIGHT(B2,10),B2) D2: =IF(AND(LEN(C2)=10,ISNUMBER(--C2),LEFT(C2,1)>="2",MID(C2,4,1)>="2"),"+1"&C2,"CHECK") 

This version does not handle extensions. Rows with x or ext in them will show CHECK, which is what you want: they go to the review pile.

Step 3: Freeze the values and review the CHECK rows

Copy the result column and use Paste Special > Values so the output no longer depends on the formula. Filter for CHECK and fix those rows at the source.

Test both formulas on a few sample rows in your own version of Excel before running them on a full list.

Pitfalls that cause most bad imports

Scientific notation

A number like 2015550101 in a column formatted as General can display as 2.01556E+09. If the file is saved as CSV in that state, the saved text can be the rounded display value, and the original digits are gone. You cannot recover them from the file: go back to the source export. Both Python functions above flag this pattern instead of trying to guess.

Lost leading zeros and the trailing ".0"

US phone numbers do not start with 0, but ZIP codes and record IDs in the same file often do, and the same tools that damage them damage phones. Reading a CSV without dtype=str in pandas can turn 2015550101 into 2015550101.0 once a blank cell forces the column to float. Read everything as text and convert only what you mean to convert.

Extensions glued to the number

201-555-0105 x12 stripped to digits becomes 201555010512, a 12-digit number that is neither valid nor obviously wrong at a glance. Split the extension off before removing non-digits, as both Python versions do, and keep it in its own column.

Excel reading the plus sign back as a number

If you open a normalized CSV by double-clicking it, Excel may read +12015550101 as the number 12015550101 and drop the +. Check the result in your CRM's import preview, or open the file with the Text column setting from Step 1, rather than trusting a double-click.

Cells that start with +, =, - or @

Some spreadsheet and import tools treat text that starts with these characters as a possible formula. Some export tools prefix such values with an apostrophe or a space to make them safe. Look at the import preview to see whether your CRM stores +12015550101 exactly or with an extra character.

One number per cell

If a source cell holds two numbers (201-555-0101 / 201-555-0102), every method above will flag it as invalid or wrong length. Split those into separate phone columns first.

A quick pre-import checklist

[ ] Original phone column kept unchanged [ ] phone_e164, phone_ext and phone_status columns added [ ] Every non-ok row reviewed or fixed at the source [ ] File opened with phone and ID columns as Text, never double-clicked [ ] Small test import done and checked in the CRM before the full import 

A note on calling and texting

Normalizing a number says nothing about whether you may call or text it. Do Not Call registries, consent requirements under the TCPA, and state rules all apply separately. Check the current FTC and FCC guidance and talk to qualified counsel about your own use. This post is about data formatting only and is not legal advice.

Disclosure: I work on Locate Next, skip tracing software for real estate teams, where we deal with phone columns like these in uploaded property lists: https://www.locatenext.com. This post contains no affiliate links.