How to clean skip trace phone numbers to E.164 before a CRM or dialer import (Python and Excel)

All owners, addresses and phone numbers below are fictional. Phone numbers use the 555-0100 to 555-0199 range, which is reserved for fictional use.

Why normalize before the import

Skip trace results, driving-for-dollars sheets, and absentee owner or tax-delinquent lists (all fictional in the examples below) arrive with phone columns in every shape before they go into your dialer, texting tool, or REI CRM: (201) 555-0101, 201.555.0102, 1-201-555-0103, 201-555-0105 x12. Most tools match, deduplicate and dial more reliably when every number uses one format. 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 owner records and numbers that were silently rejected. If you do it in the file first, you can see every row that did not convert and decide what to do with it.

There are three ways to do it: Python with the phonenumbers library (recommended), a Python fallback for US numbers that needs no extra packages, and an Excel formula for lists that never leave 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 number a status (ok, blank, invalid and so on) so nothing disappears quietly
• Keep each phone number only once, even when it shows up on several owner records

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.02E+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.

is_valid_number checks the number against known ranges. That is stricter than is_possible_number, which only checks length. Keep phonenumbers updated so it recognizes newly assigned area codes, and look at the invalid rows before deleting anything.

Applying it to an owner list with pandas

A typical skip traced owner list (fictional) looks like this:

owner_name,property_address,phone1,phone2,phone3
Dana Example,12 Elm St,(201) 555-0101,201.555.0102,
Sam Sample,48 Oak Ave,1-201-555-0103,201-555-0105 x12,(201) 555-0101

Reshape it to one row per owner per phone column, then normalize every number:

import re

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("owner_list.csv", dtype=str, keep_default_na=False)
df["owner_row"] = df.index
phone_cols = [c for c in df.columns if re.fullmatch(r"phone\d+", c)]

phones = df.melt(
    id_vars=["owner_row", "owner_name", "property_address"],
    value_vars=phone_cols,
    var_name="source_column",
    value_name="raw_phone",
)
results = phones["raw_phone"].map(normalize_phone)
phones["phone_e164"] = results.map(lambda r: r[0])
phones["phone_ext"] = results.map(lambda r: r[1])
phones["phone_status"] = results.map(lambda r: r[2])

print(phones["phone_status"].value_counts())

review = phones[~phones["phone_status"].isin(["ok", "blank"])]
review.to_csv("phones_to_review.csv", index=False)

The original file is untouched, and source_column tells you which column each number came from. The review file holds every number that did not convert, so someone can fix the source instead of guessing.

Multiple phones per owner

Skip trace results often repeat a number in phone1 and phone3. They can also attach the same number to two owners, such as spouses, an LLC and its manager, or one owner with several properties. Drop the repeats after normalizing, because (201) 555-0101 and 201.555.0101 only match once both are +12015550101:

ok = phones[phones["phone_status"] == "ok"]

# Same number twice on one owner record
ok = ok.drop_duplicates(subset=["owner_row", "phone_e164"])

# Same number on more than one owner record: keep the first, set the rest aside
dupes = ok[ok.duplicated(subset=["phone_e164"], keep="first")]
ok = ok.drop(dupes.index)

dupes.to_csv("phones_duplicate_across_owners.csv", index=False)
ok.to_csv("owner_phones_clean.csv", index=False)

In the fictional sample, (201) 555-0101 appears for both owners. It stays on Dana Example, and the copy on Sam Sample goes to the duplicates file. Check that file before importing: sometimes the shared number is correct for both records, and you want to link them in your CRM rather than lose one.

Option 2: a fallback for US numbers with no extra packages

If you cannot install packages, use this function. It checks the format of North American Numbering Plan numbers using only the Python standard library. It returns the same values as normalize_phone, so you can use it in the pandas code above.

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, including Canada and several Caribbean countries, so +1 alone does not prove a number is in the US.
• International numbers come back as wrong_length. Use phonenumbers for mixed lists.

Option 3: Excel

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

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

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. Repeat for each phone column.

=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 #. Everything from there on is treated as the extension
• chars splits the remaining text into single characters, and digits keeps only the 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,""))
  ))
))

Step 2 for older Excel versions

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

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, so rows with x or ext show CHECK and go to the review pile.

Step 3: Freeze the values, review, and remove duplicates

Copy the result columns and use Paste Special > Values. Filter for CHECK and fix those rows at the source. To catch repeats, stack the normalized phone columns into one column and use Data > Remove Duplicates, or highlight them with conditional formatting for duplicate values. Test the 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

In a narrow General column, or with numbers longer than 11 digits (for example, numbers with an extension or a stray country code glued on), Excel can display and then save values like 2.02E+09. If the file is saved as CSV in that state, the original digits can be lost, and you cannot recover them from the file. Go back to the source export or skip trace results instead. Both Python functions flag this pattern instead of guessing.

The trailing ".0"

If you read a CSV in pandas without dtype=str, a blank cell can force the column to float and turn 2015550101 into 2015550101.0. The same mistake damages ZIP codes and parcel IDs that start with zero. 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. That 12-digit number is not valid, but at a glance it does not look obviously wrong either. Split the extension off before removing non-digits, and keep it in its own column.

Excel dropping the plus sign

If you double-click a normalized CSV, Excel may read +12015550101 as 12015550101 and drop the +. Check the result in your CRM or dialer's import preview, and confirm whether it stores the value exactly or adds a leading apostrophe or space.

A quick pre-import checklist

[ ] Original phone columns kept unchanged
[ ] e164, extension and status added for every phone column
[ ] Every non-ok number reviewed or fixed at the source
[ ] Cells holding two numbers split into separate phone columns
[ ] Duplicates removed within each owner and across the file
[ ] File opened with phone and ID columns as Text, never double-clicked
[ ] Small test import checked in the CRM or dialer before the full import

A note on calling and texting

Normalizing a number does not tell you whether you may call or text it. Do Not Call registries, TCPA consent requirements and state rules all apply separately. They cover cold calling, texting campaigns and dialers alike. Check the current FTC and FCC guidance, and check the rules that apply to you with your counsel. This post covers data formatting only and is not legal advice.

Published by Locate Next (locatenext.com).