REGEXTEST, REGEXEXTRACT and REGEXREPLACE Equivalents in pandas

Excel finally has regular expressions: REGEXTEST checks whether text matches a pattern, REGEXEXTRACT pulls matching text out, and REGEXREPLACE rewrites it. Spreadsheet users are now writing formulas like =REGEXEXTRACT(A2, "INV-\d{4}-\d+") to fish invoice numbers out of free-text notes. pandas has offered the same operations for years through its .str methods, so translating these formulas is mostly mechanical — but the arguments are arranged differently, replacement strings use a different syntax for groups, and Excel's "nth occurrence" option has no direct pandas parameter. This page maps each function, shows the differences on a realistic column and gives tested pandas code. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

payment_notes.xlsx has one column of free-text payment notes. Some mention one invoice number, one mentions two, one writes the prefix in lower case, one has none, one is empty. The Excel functions discussed here are available in Excel 365; LibreOffice does not evaluate them (version 26.2 returned #NAME?), so the spreadsheet behaviour described follows Excel's documentation.

Step 1 — REGEXTEST is str.contains

=REGEXTEST(A2, "INV-\d{4}-\d+") returns TRUE or FALSE. Its optional third argument chooses case sensitivity: 0 (the default) is case-sensitive, 1 ignores case.

import pandas as pd

df = pd.read_excel("payment_notes.xlsx", keep_default_na=False)
pattern = r"INV-\d{4}-\d+"
df["HasInvoice"] = df["Note"].str.contains(pattern, regex=True)
df["HasInvoice (any case)"] = df["Note"].str.contains(pattern, regex=True, case=False)
print(df[["HasInvoice", "HasInvoice (any case)"]].to_string())

The case-sensitive test misses the note that wrote inv-2026-0107 in lower case — but still returns TRUE for that row, because the same note also contains INV-2026-0108. case=False is the equivalent of passing 1. Use a raw string (r"…") for patterns in Python so that \d reaches the regex engine intact. keep_default_na=False keeps the empty note as an empty string rather than NaN; with NaN in the column, str.contains returns NaN for that row instead of False — pass na=False when you cannot control the input.

Step 2 — REGEXEXTRACT's three return modes

REGEXEXTRACT(text, pattern, return_mode) returns the first match (mode 0, the default), all matches as an array (mode 1), or the capture groups of the first match as an array (mode 2). Each has a pandas counterpart:

first = df["Note"].str.extract(r"(INV-\d{4}-\d+)", expand=False, flags=0)
all_matches = df["Note"].str.findall(r"INV-\d{4}-\d+")
groups = df["Note"].str.extract(r"INV-(?P<Year>\d{4})-(?P<Number>\d+)")
print(pd.concat([first.rename("first match"), all_matches.rename("all matches"), groups], axis=1).to_string())

str.extract returns the first match of the first capture group, so the whole pattern is wrapped in parentheses for mode 0. str.findall returns a list per row — mode 1. With named groups, str.extract returns one column per group — mode 2, but with headers, which a spreadsheet array cannot have. Rows without a match give NaN (or an empty list from findall), where Excel returns #N/A; wrap the formula in IFERROR in Excel and use fillna in pandas to show something friendlier.

Step 3 — Case-insensitive extraction

str.extract and str.findall take regular-expression flags instead of a case argument:

import re

any_case = df["Note"].str.findall(r"INV-\d{4}-\d+", flags=re.IGNORECASE)
normalised = any_case.map(lambda xs: [x.upper() for x in xs])
print(normalised.to_string())
print("distinct invoices:", sorted({x for xs in normalised for x in xs}))

With re.IGNORECASE the lower-case inv-2026-0107 is found too, and upper-casing the matches gives one spelling per invoice. Exploding the lists into rows — one row per invoice mentioned — is a common next step for matching notes against an invoice table, shown in Split Delimited Cell Values into Rows with pandas.

Step 4 — REGEXREPLACE is str.replace, with a different group syntax

=REGEXREPLACE(A2, "INV-(\d{4})-(\d+)", "$2/$1") rewrites each invoice number as number/year. Excel refers to groups as $1, $2; Python's re uses \1, \2 (or \g<1>):

swapped = df["Note"].str.replace(r"INV-(\d{4})-(\d+)", r"\2/\1", regex=True)
print(swapped.to_string())

masked = df["Note"].str.replace(r"\b[\w.]+@[\w.]+\b", "[email removed]", regex=True)
masked = masked.str.replace(r"\b\d{4} \d{3} \d{4}\b", "[phone removed]", regex=True)
print(masked.iloc[[3, 5]].to_string())

Copying a replacement from an Excel formula without changing $1 to \1 inserts the literal text $1 — no error, just wrong output, so it is worth a test. The second example is a typical clean-up job: masking email addresses and phone numbers before a file is shared, as in Anonymise Personal Data in an Excel File with Python.

Step 5 — Replacing only the nth occurrence

REGEXREPLACE's fourth argument, occurrence, replaces only the nth match (0, the default, replaces all; negative numbers count from the end). str.replace has n, but that limits the number of replacements from the start, so it covers only the first occurrence. For "the second" or "the last", use a function:

def replace_occurrence(text, pattern, repl, occurrence, flags=0):
    matches = list(re.finditer(pattern, text, flags))
    if not matches or occurrence == 0:
        return re.sub(pattern, repl, text, flags=flags)
    index = occurrence - 1 if occurrence > 0 else len(matches) + occurrence
    if not 0 <= index < len(matches):
        return text
    m = matches[index]
    return text[:m.start()] + m.expand(repl) + text[m.end():]

note = df.loc[1, "Note"]
print(replace_occurrence(note, r"INV-\d{4}-\d+", "<second>", 2, re.I))
print(replace_occurrence(note, r"INV-\d{4}-\d+", "<last>", -1, re.I))
print(df["Note"].map(lambda t: replace_occurrence(t, r"INV-\d{4}-\d+", "<first>", 1, re.I)).iloc[1])

m.expand(repl) applies \1-style group references in the replacement, so the helper accepts the same replacement strings as str.replace.

Step 6 — Patterns that behave differently

Excel uses the PCRE2 regular-expression library; Python uses its own re module. Everyday patterns — character classes, quantifiers, groups, anchors, \d, \w, \b — work the same. A few constructs do not:

tests = {
    r"\d+(?=-\d)": "lookahead (both support it)",
    r"(?i)inv": "inline case flag (both)",
    r"\p{Lu}+": "Unicode property (PCRE2 only)",
}
for pat, label in tests.items():
    try:
        print(f"{pat:14} {label:30} -> {re.findall(pat, 'INV-2026-0042 ÄRGER')}")
    except re.error as exc:
        print(f"{pat:14} {label:30} -> re.error: {exc}")

Unicode property classes such as \p{Lu} (upper-case letters in any script) are PCRE2 features that Python's re rejects; the third-party regex package supports them with the same API. Python's \w and \d are Unicode-aware by default, so they also match accented letters and non-ASCII digits — usually desirable, but pass flags=re.ASCII to mirror patterns that assume ASCII. When translating a formula, run its pattern against a few known inputs in both places before trusting it on the whole column.

Step 7 — Match extracted codes against the real list

Extraction is rarely the end of the job. The usual next question is "which of these notes mention an invoice we do not recognise?" — a typo, a cancelled invoice, a number from another system. Explode the matches into one row per mention and compare them with the invoice register:

register = pd.DataFrame({"Invoice": ["INV-2026-0042", "INV-2026-0107", "INV-2026-0108", "INV-2026-0200"]})
mentions = (df.assign(Invoice=normalised)
              .explode("Invoice")
              .dropna(subset=["Invoice"])[["Invoice", "Note"]])
checked = mentions.merge(register.assign(Known=True), on="Invoice", how="left")
checked["Known"] = checked["Known"].fillna(False).astype(bool)
print(checked[["Invoice", "Known"]].to_string(index=False))

Four of the five mentions are in the register; INV-2025-0991 is not, and that row is the one to send back for checking. Doing this as an exploded table rather than inside a formula keeps every mention visible — a note that names two invoices produces two rows, so neither can hide behind the other. The register comparison is an ordinary left merge; Find Rows in One Excel File Missing from Another covers the variations.

Common pitfalls

• $1 in replacements. Python needs \1 or \g<1>; $1 is inserted literally.
• Forgetting raw strings. "\d" works by luck, "\b" becomes a backspace character; write r"\b".
• NaN in the column. String methods return NaN for those rows; use na=False with contains, or fill first.
• str.extract without a group. It raises ValueError: pattern contains no capture groups; wrap the pattern in parentheses.
• Case defaults. Excel's default is case-sensitive, like pandas; ignoring case is opt-in on both sides.

Conclusion

REGEXTEST is str.contains, REGEXEXTRACT is str.extract for the first match or capture groups and str.findall for every match, and REGEXREPLACE is str.replace(regex=True) — with $1 rewritten as \1, case-insensitivity passed as case=False or re.IGNORECASE, and a small re.finditer helper for replacing only the nth occurrence. Use raw strings, handle NaN rows explicitly, and test patterns that rely on PCRE2-only features, which Python's re does not support.

Frequently asked questions

What is the pandas equivalent of Excel REGEXTEST? Series.str.contains(pattern, regex=True), which returns True or False per row. Pass case=False to ignore case, like REGEXTEST's case_sensitivity argument of 1, and na=False if the column contains missing values.

How do I get all regex matches from a column like REGEXEXTRACT with return mode 1? Use Series.str.findall(pattern), which returns a list of every match per row. Add flags=re.IGNORECASE for case-insensitive matching and explode the result to get one match per row.

Why does my REGEXREPLACE pattern insert $1 literally in pandas? Excel writes group references as $1 and $2, but Python's re module uses \1 and \2 or \g<1>. Change the replacement string to r"\2/\1".

How do I replace only the second match in a string with pandas? str.replace can only limit replacements from the start with n. Use re.finditer to find all matches and rebuild the string around the chosen one, as the replace_occurrence helper on this page does.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to pandas.
• Extract Numbers and Codes from Text with pandas — practical extraction recipes.
• FIND, SEARCH and SUBSTITUTE Equivalents in pandas — the non-regex text functions.
• Validate Email and Phone Formats in Excel Data — patterns for validation.