FIND, SEARCH and SUBSTITUTE Equivalents in pandas

Excel's text-search functions are the building blocks of every "clean up this column" formula: FIND locates a character, SEARCH does the same ignoring case, SUBSTITUTE swaps one string for another and REPLACE overwrites a position. Combined with MID and LEN, they pull invoice numbers out of references and normalise phone numbers — in formulas that are notoriously hard to read. pandas has direct equivalents in its .str methods, with two differences that trip up everyone translating formulas: positions start at 0, and "not found" is -1 rather than an error. This page, part of Excel Formula Equivalents in pandas, translates each function and the patterns people build from them.

Prerequisites

pip install pandas openpyxl

refs.xlsx has four document references — mixed case, one without a customer in brackets, one with two bracketed parts — and four phone numbers written four different ways.

Step 1 — FIND: positions start at 0, and -1 means not found

str.find returns the position of the first match, counting from 0, or -1 when the text is absent. Excel's FIND counts from 1 and returns #VALUE! when the text is absent. Convert explicitly:

import re
import numpy as np
import pandas as pd

refs = pd.read_excel("refs.xlsx")
pos0 = refs["ref"].str.find("(")
refs["find_excel"] = (pos0 + 1).where(pos0 >= 0)            # like FIND("(", A2), NaN for #VALUE!
print(refs[["ref", "find_excel"]].to_string(index=False))

The reference without a customer gets NaN where Excel would show #VALUE!. Keeping the -1 as a number would be dangerous: MID-style slicing with a -1 position silently takes text from the end of the string. str.rfind finds the last occurrence, which Excel can only do with a convoluted SUBSTITUTE trick.

Step 2 — SEARCH: ignore case, and handle wildcards

SEARCH is FIND without case sensitivity. Lower-case both the text and the search term:

refs["search_inv"] = refs["ref"].str.lower().str.find("inv") + 1
print(refs[["ref", "search_inv"]].to_string(index=False))

Both INV-… and inv-… now report position 1, and the credit note reports 0 — which, remember, means not found once 1 is added. SEARCH also understands wildcards: ? for one character and * for any run. Python's fnmatch.translate converts such a pattern to a regular expression:

from fnmatch import translate

pattern = re.sub(r"\\[Zz]$", "", translate("inv-2026-00??*")).replace("(?s:", "(?:")
print(pattern)
print(refs["ref"].str.contains(pattern, case=False, regex=True).tolist())

fnmatch.translate anchors the pattern to the end of the string, like a file-name match; removing the end anchor (\Z or \z, depending on the Python version) makes it behave like SEARCH. For anything more complex, write the regular expression directly — r"INV-2026-\d{4}" says exactly what it means.

Step 3 — SUBSTITUTE: replace text literally

SUBSTITUTE(A2, "-", "") replaces every occurrence of the old text. str.replace with regex=False does the same, and several substitutions chain naturally — the pandas form of nested SUBSTITUTE calls:

digits = refs["phone"]
for old in [" ", "-", ".", "(", ")"]:
    digits = digits.str.replace(old, "", regex=False)
refs["phone_clean"] = digits.str.replace(r"^\+44", "0", regex=True)
print(refs[["phone", "phone_clean"]].to_string(index=False))

Four differently written numbers become one format. The last line switches to a regular expression on purpose, to replace +44 only at the start. Keep regex=False for literal replacements: with a pattern, . matches any character and ( starts a group, so str.replace(".", "", regex=True) would delete every character. When a literal string has to go into a pattern, wrap it in re.escape.

Long chains of nested SUBSTITUTE calls in Excel — six or eight deep to standardise company suffixes — become a dictionary in pandas, which is far easier to read and extend. Use word boundaries so a replacement only hits whole words:

SUFFIXES = {"Limited": "Ltd", "Ltd.": "Ltd", "Incorporated": "Inc", "&": "and"}
names = pd.Series(["Acme Limited", "Acme Ltd.", "Smith & Jones Incorporated", "Delimited Holdings"])
clean = names
for old, new in SUFFIXES.items():
    clean = clean.str.replace(rf"(?<!\w){re.escape(old)}(?!\w)", new, regex=True)
print(clean.tolist())

"Delimited Holdings" is untouched: a plain SUBSTITUTE(A2,"Limited","Ltd") would have produced "DeLtd Holdings", because it replaces inside words. The lookarounds (?<!\w) and (?!\w) act as word boundaries that also work for replacements ending in punctuation, such as Ltd., where \b would not. Keep the dictionary in a configuration file or a sheet so the business can add entries without a code change.

Step 4 — SUBSTITUTE with an instance number

SUBSTITUTE's fourth argument replaces only the nth occurrence — =SUBSTITUTE(A2, "(", "[", 2) changes the second bracket and leaves the first. str.replace has no such argument, but splitting on the text and rejoining does it cleanly:

def substitute_nth(text, old, new, n):
    parts = text.split(old)
    if len(parts) <= n:
        return text                       # fewer than n occurrences: unchanged, as in Excel
    return old.join(parts[:n]) + new + old.join(parts[n:])

print([substitute_nth(t, "(", "[", 2) for t in refs["ref"]])

Only the last reference has a second bracket, and only that one changes. str.replace(old, new, n=1) covers the common "first occurrence only" case directly.

Count occurrences and find the nth one

Two classic Excel tricks exist only because Excel lacks the functions. Counting how often a character appears is written =(LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))/LEN("-"); in pandas it is str.count. Finding the second hyphen needs FIND over a SUBSTITUTE that marks it with CHAR(1); in pandas, iterate over the matches:

print(refs["ref"].str.count(re.escape("-")).tolist())

def find_nth(text, sub, n):
    positions = [m.start() + 1 for m in re.finditer(re.escape(sub), text)]
    return positions[n - 1] if len(positions) >= n else np.nan

print([find_nth(t, "-", 2) for t in refs["ref"]])

Each reference has two hyphens except the reissued one, which has three; the second hyphen is at position 9 in all of them, Excel-style. str.count takes a regular expression, which is why the hyphen is passed through re.escape — harmless here, essential for characters such as . or (. find_nth returns 1-based positions to match Excel, and NaN when there are fewer than n matches.

Step 5 — REPLACE by position, and MID(FIND()) for text between delimiters

Excel's REPLACE overwrites characters at a position — =REPLACE(A2, 1, 3, "BIL") swaps the first three characters. str.slice_replace takes 0-based start and stop positions:

print(refs["ref"].str.slice_replace(0, 3, "BIL").tolist())

The most common combination in real workbooks is MID with two FINDs to take the text between delimiters — =MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1). In pandas, str.extract with a pattern states the same thing directly and handles the "not found" case without errors:

refs["customer"] = refs["ref"].str.extract(r"\(([^)]*)\)", expand=False)
refs["year"] = refs["ref"].str.extract(r"-(\d{4})-", expand=False).astype("Int64")
print(refs[["ref", "customer", "year"]].to_string(index=False))

The credit note gets NaN for the customer instead of #VALUE!, and the reissued invoice yields its first bracketed part, Fabrikam, because [^)]* stops at the first closing bracket. Extract Numbers and Codes from Text with pandas covers extraction patterns in depth.

Common pitfalls

• Off-by-one positions. str.find is 0-based; add 1 to compare with Excel.
• Using -1 as a position. Slicing with -1 takes text from the end; convert to NaN first.
• Regex characters in str.replace. Leave regex=False for literal text; escape with re.escape when mixing.
• Case in SEARCH translations. Lower-case both sides, or pass case=False to str.contains; forgetting it is the most common reason a translated formula finds fewer rows than Excel did.

Performance notes

The .str methods loop in optimised code and handle hundreds of thousands of strings per second. Chaining five str.replace calls is still fast; for dozens of substitutions, one regular expression with alternatives — str.replace(r"[\s\-.()]", "", regex=True) — does a single pass. Row-wise helpers such as substitute_nth run in Python and are best kept to the columns that need them.

Conclusion

FIND becomes str.find(...) + 1 with -1 converted to missing, SEARCH a find on lower-cased text or a case-insensitive regex (with fnmatch.translate for wildcards), SUBSTITUTE a literal str.replace — chained for nested substitutions, with a split-and-join helper for the instance argument — and REPLACE str.slice_replace. The ubiquitous MID(FIND()) pattern is best rewritten as str.extract, which is shorter and fails gracefully.

Frequently asked questions

Why does str.find return a different number from FIND? Excel positions start at 1 and pandas positions start at 0, so str.find returns one less. A missing substring is #VALUE! in Excel and -1 in pandas. Add 1 and treat -1 as missing to reproduce FIND exactly.

What is the difference between FIND and SEARCH? FIND is case-sensitive and SEARCH is not; SEARCH also accepts the wildcards ? and *. In pandas, lower-case both sides for a case-insensitive search, and convert wildcards to a regular expression with fnmatch.translate.

How do I replace only the second occurrence, like SUBSTITUTE's instance argument? str.replace replaces all occurrences, or the first n with n=. For a specific occurrence, split on the text and rejoin, or use a regular expression that skips the earlier matches, as shown on this page.

Does str.replace treat the pattern as a regular expression? Not by default in current pandas: regex defaults to False, so characters such as . and ( are matched literally. Pass regex=True when you want a pattern, and escape special characters with re.escape when a literal string must go into one.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas — position-based text functions.
• Find and Replace Text in Excel with openpyxl — replacing text inside a workbook rather than a DataFrame.
• Strip Whitespace and Normalise Text Columns with pandas — the clean-up that usually comes first.