TEXTBEFORE, TEXTAFTER and TEXTSPLIT Equivalents in pandas

Excel's newer text functions replaced a generation of LEFT(A2, FIND("@", A2) - 1) formulas. TEXTBEFORE(A2, "@") returns the part of an email address before the at-sign; TEXTAFTER(A2, " - ", -1) the part after the last separator; TEXTSPLIT(A2, ",") spills each comma-separated value into its own column. pandas has string methods for all of these — str.partition, str.rpartition, str.split with a limit — and they are fast and vectorised. But they differ from Excel in one important way: what happens when the delimiter is not there. Excel returns #N/A unless told otherwise; str.partition quietly returns the whole text, which can pass an address without an at-sign through as a "username". This page maps each function and its options to pandas, handles the missing-delimiter case the way Excel does, and checks every result against the spreadsheet. It belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl

records.xlsx holds four records with an email address (one missing its at-sign), a location made of city, postcode and country separated by " - ", a comma-separated tag list with inconsistent spacing and one empty value, and a product code with mixed-case parts. LibreOffice (soffice) recalculates the equivalent spreadsheet formulas for comparison; the newer functions are written with the _xlfn. prefix the file format requires.

Step 1 — The spreadsheet's answers

Write the formulas for each record and recalculate, to have reference results:

import subprocess
from openpyxl import load_workbook

wb = load_workbook("records.xlsx")
ws = wb.active
heads = ["user", "domain", "city", "country", "middle", "user or n/a", "after first a (any case)"]
for j, h in enumerate(heads, start=5):
    ws.cell(1, j, h)
for r in range(2, ws.max_row + 1):
    ws.cell(r, 5, f'=_xlfn.TEXTBEFORE(A{r},"@")')
    ws.cell(r, 6, f'=_xlfn.TEXTAFTER(A{r},"@")')
    ws.cell(r, 7, f'=_xlfn.TEXTBEFORE(B{r}," - ")')
    ws.cell(r, 8, f'=_xlfn.TEXTAFTER(B{r}," - ",-1)')
    ws.cell(r, 9, f'=_xlfn.TEXTBEFORE(_xlfn.TEXTAFTER(B{r}," - ")," - ")')
    ws.cell(r, 10, f'=_xlfn.TEXTBEFORE(A{r},"@",,,,"n/a")')
    ws.cell(r, 11, f'=_xlfn.TEXTAFTER(D{r},"a",1,1)')
wb.save("records_formulas.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "calc", "records_formulas.xlsx"], check=True, capture_output=True)
calc = load_workbook("calc/records_formulas.xlsx", data_only=True).active
excel = [[calc.cell(r, c).value for c in range(5, 12)] for r in range(2, calc.max_row + 1)]
for row in excel:
    print(row)

The results show the behaviours to match: the user and domain parts of each email, #N/A for "chloe.martin" which has no at-sign — and "n/a" where the formula supplies its if_not_found argument — the city before the first separator, the country after the last one (instance -1), the postcode between them, and a case-insensitive match (match_mode 1) that finds the "a" in "SKU-a-104" and the "A" in "SKU-A-205" alike.

Step 2 — Before and after the first or last delimiter

The first occurrence is str.partition, the last is str.rpartition. Each returns three parts — before, the delimiter, after — and the missing-delimiter case needs one extra step to behave like Excel:

import pandas as pd

df = pd.read_excel("records.xlsx", keep_default_na=False)

def text_before(s, delim, last=False, if_not_found=None):
    parts = (s.str.rpartition(delim) if last else s.str.partition(delim))
    found = s.str.contains(delim, regex=False)
    return parts[0].where(found, if_not_found)

def text_after(s, delim, last=False, if_not_found=None):
    parts = (s.str.rpartition(delim) if last else s.str.partition(delim))
    found = s.str.contains(delim, regex=False)
    return parts[2].where(found, if_not_found)

print(df["Email"].str.partition("@")[0].tolist(), "← plain partition")
df["user"] = text_before(df["Email"], "@")
df["domain"] = text_after(df["Email"], "@")
df["city"] = text_before(df["Location"], " - ")
df["country"] = text_after(df["Location"], " - ", last=True)
print(df[["user", "domain", "city", "country"]].to_string(index=False))

The first line shows the trap: plain partition returns "chloe.martin" as the part before an at-sign that does not exist — a value that looks perfectly valid and would sail through into a "username" column. Checking with str.contains(..., regex=False) and replacing the not-found rows with None reproduces Excel's #N/A as a missing value, or any if_not_found default. rpartition searches from the end, which is Excel's instance_num = -1: the country "UK" after the last " - ". regex=False matters because delimiters such as "." or "|" mean something else in a regular expression.

Step 3 — The nth occurrence, and case-insensitive matching

For the nth occurrence, split with a limit and rejoin; for case-insensitive matching, search a lower-cased copy and cut the original at the same position:

def text_after_nth(s, delim, n, if_not_found=None):
    parts = s.str.split(delim, n=n, regex=False)
    return parts.map(lambda p: p[n] if len(p) > n else if_not_found)

def text_before_nth(s, delim, n, if_not_found=None):
    parts = s.str.split(delim, regex=False)
    return parts.map(lambda p: delim.join(p[:n]) if len(p) > n else if_not_found)

def text_after_ci(s, delim, if_not_found=None):
    pos = s.str.lower().str.find(delim.lower())
    return pd.Series([t[i + len(delim):] if i >= 0 else if_not_found for t, i in zip(s, pos)], index=s.index)

df["middle"] = text_before(text_after(df["Location"], " - "), " - ")
df["after 2nd sep"] = text_after_nth(df["Location"], " - ", 2)
df["after first a"] = text_after_ci(df["Code"], "a")
print(df[["Location", "middle", "after 2nd sep", "Code", "after first a"]].to_string(index=False))

str.split(delim, n=2) splits at most twice, so element 2 is everything after the second delimiter — TEXTAFTER(..., 2). The middle field is "after the first, before the next", exactly as the nested Excel formula reads. For case-insensitive matching, str.find on the lower-cased text gives each match's position, and slicing the original text at that position keeps the original capitals in the result — "SKU-a-104" and "SKU-A-205" both yield the text after their first "a" in any case. (Note that "SKU" contains no "a", so the first match is the one in the middle.)

Step 4 — TEXTSPLIT into columns, or into rows

TEXTSPLIT(text, column_delimiter) spills values across columns; with a row delimiter, or when the next step is analysis, rows are often the more useful shape:

tags_cols = df["Tags"].str.split(",", expand=True).apply(lambda c: c.str.strip())
print(tags_cols.to_string())

tags_rows = (df.assign(Tag=df["Tags"].str.split(","))
               .explode("Tag")
               .assign(Tag=lambda d: d["Tag"].str.strip())
               .query("Tag != ''")[["Email", "Tag"]])
print(tags_rows.to_string(index=False))
print(tags_rows["Tag"].value_counts().to_dict())

str.split(",", expand=True) gives one column per position, padding shorter lists with None — the same rectangle TEXTSPLIT spills, with Excel's pad_with behaviour built in. Stripping each piece fixes the inconsistent spaces around commas. For counting or filtering by tag, explode turns each list into one row per tag, and dropping empty strings removes the record whose tag list was blank. The rows shape is what makes "how many records are tagged tools?" a one-liner — two records here, once the stray space in "logistics " has been stripped; the columns shape is what a spreadsheet user expects to see. More on this in Split Delimited Cell Values into Rows with pandas.

Step 5 — Check against the spreadsheet

Compare every pandas result with the recalculated formulas, treating Excel's #N/A and a missing value as the same:

ours = pd.DataFrame({
    "user": df["user"], "domain": df["domain"], "city": df["city"], "country": df["country"], "middle": df["middle"],
    "user or n/a": text_before(df["Email"], "@", if_not_found="n/a"), "after first a": df["after first a"],
})
def same(a, b):
    return (pd.isna(a) and str(b).startswith("#")) or a == b
checks = [same(ours.iat[i, j], excel[i][j]) for i in range(len(ours)) for j in range(ours.shape[1])]
print(f"{sum(checks)}/{len(checks)} values match the spreadsheet")

All 28 values — seven formulas for four records — match once a missing value on the pandas side is treated as the counterpart of Excel's #N/A, including the not-found case in both its #N/A and if_not_found forms. Keeping such a comparison while translating a workbook pays off: text functions have many small options, and the one row where a delimiter is missing or doubled is precisely the row a quick visual check misses. The older LEFT/MID/FIND style is covered in Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas.

Common pitfalls

• Trusting str.partition when the delimiter is missing. It returns the whole text; check with contains.
• Regex delimiters. Pass regex=False so "." and "|" are taken literally.
• 0-based versus 1-based instances. split(n=2)[2] is the text after the second delimiter.
• Case-insensitive search that changes the text. Find on a lower-cased copy, slice the original.
• Untrimmed split pieces. Strip spaces after splitting on commas.

Conclusion

TEXTBEFORE and TEXTAFTER map onto str.partition for the first delimiter and str.rpartition for the last, str.split with a limit for the nth, and a find on a lower-cased copy for case-insensitive matching; TEXTSPLIT maps onto str.split(expand=True) for columns and explode for rows. The one behaviour to add by hand is Excel's handling of a missing delimiter: test with str.contains(regex=False) and return a missing value or an explicit default instead of the whole text. Check the translation against the spreadsheet's results, including the rows where the delimiter is absent.

Frequently asked questions

What is the pandas equivalent of TEXTBEFORE? Use str.partition with the delimiter and take the first part for the first occurrence, or str.rpartition for the last. Check with str.contains first, because partition returns the whole text when the delimiter is missing.

How do I get the text after the last delimiter in pandas? Use str.rpartition with the delimiter and take the third part, which corresponds to TEXTAFTER with an instance number of -1.

What happens in pandas when the delimiter is not found? str.partition puts the whole text in the first part and empty strings in the others. Excel returns #N/A, so replace not-found rows with a missing value or a default to match.

How do I split a cell into rows instead of columns? Split with str.split into lists, then use DataFrame.explode on that column to get one row per item.

Related

• Up one level: Excel Formula Equivalents in pandas — spreadsheet functions in Python.
• FIND, SEARCH and SUBSTITUTE Equivalents in pandas — positions and replacements.
• REGEXEXTRACT, REGEXTEST and REGEXREPLACE Equivalents in pandas — patterns instead of fixed delimiters.
• Split a Column into Multiple Columns with pandas — the same task from the cleaning side.