Python Excel AutomationPractical guides for developers
Start learning
Guide
Advanced Data Transformation And CleaningDeep dive

Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas

Slicing replaces LEFT, RIGHT and MID; + and str.cat replace CONCATENATE and TEXTJOIN. Plus split, extract and the regex work that has no formula equivalent.

Excel's text functions exist because a formula operates on one value at a time. pandas operates on whole columns, so LEFT, RIGHT and MID collapse into ordinary slicing and CONCATENATE becomes an operator — and the string methods that have no spreadsheet equivalent, like splitting a column into several or applying a regular expression, come along for free. This guide is part of Excel Formula Equivalents in pandas.

Excel text functions and their column-wide counterparts LEFT, RIGHT and MID all become slice syntax on the string accessor, CONCATENATE becomes the plus operator, TEXTJOIN becomes str.cat with a placeholder, and TRIM needs both strip and a whitespace collapse. Excel pandas Note LEFT / RIGHT / MID .str[a:b] one syntax, three functions CONCATENATE / & + missing values propagate TEXTJOIN str.cat(na_rep=...) placeholder for blanks TRIM strip + collapse runs two steps to match SUBSTITUTE str.replace(regex=False) literal by default in Excel no equivalent str.extract(pattern) replaces nested FIND and MID the last row is where most formula disappears

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

records = pd.DataFrame({
    "Reference": ["INV-2026-000412", "INV-2026-000413", "CRN-2026-000044", "INV-2025-009871"],
    "Customer": ["  Acme Ltd ", "Brightline PLC", "cobalt systems", None],
    "Contact": ["ana@acme.example", "ben@brightline.example", None, "dev@cobalt.example"],
    "Postcode": ["SW1A 1AA", "M1 4BT", "EH8 9YL", "BS1 5TR"],
}).astype({"Reference": "string", "Customer": "string", "Contact": "string", "Postcode": "string"})

Casting to the nullable string dtype is worth doing up front: it keeps missing values as pd.NA rather than float('nan'), which makes the string methods behave consistently.

LEFT, RIGHT and MID become slicing

Python
# =LEFT(A2, 3)
records["Doc_Type"] = records["Reference"].str[:3]

# =RIGHT(A2, 6)
records["Serial"] = records["Reference"].str[-6:]

# =MID(A2, 5, 4)
records["Year"] = records["Reference"].str[4:8]

print(records[["Reference", "Doc_Type", "Year", "Serial"]])

One syntax replaces three functions, and it composes: records["Reference"].str[4:8].astype(int) turns the extracted year into a number in the same expression. The slice is also forgiving in a way MID is not — asking for more characters than the string has returns what exists rather than an error.

Where the position is not fixed, str.split is more robust than counting characters:

Python
parts = records["Reference"].str.split("-", expand=True)
parts.columns = ["Doc_Type", "Year", "Serial"]
records[["Doc_Type", "Year", "Serial"]] = parts

That is Text to Columns in one line, and unlike the fixed-width version it survives a reference format whose serial number grows a digit.

CONCATENATE, TEXTJOIN and the ampersand

Python
# =A2 & " / " & D2
records["Label"] = records["Reference"] + " / " + records["Postcode"]

# =TEXTJOIN(" | ", TRUE, A2, B2, D2) — skipping blanks
records["Summary"] = records[["Reference", "Customer", "Postcode"]].apply(
    lambda row: " | ".join(part for part in row if pd.notna(part)), axis=1
)

# str.cat with a placeholder rather than dropping the row's result
records["Joined"] = records["Reference"].str.cat(records["Customer"], sep=" — ", na_rep="(no name)")
print(records[["Label", "Joined"]])

The + operator behaves like CONCATENATE: if any part is missing, the whole result is missing. str.cat with na_rep behaves like TEXTJOIN with its ignore-empty flag, supplying a placeholder instead. Choosing between them is a decision about the report — a blank customer name that silently blanks the whole label is rarely what anyone wants.

TRIM, UPPER, PROPER and their relatives

Matching Excel's TRIM exactly Excel's TRIM removes leading and trailing spaces and also collapses internal runs to a single space, so reproducing it takes both a strip and a regular-expression replacement. 1 strip the ends .str.strip() removes leading and trailing space 2 collapse internal runs .str.replace(r'\\s+', ' ') matches TRIM 3 normalise the case .str.title() or a known-spellings lookup 4 apply to both sides a join only works if both keys were cleaned two spaces and one space are different keys to every join
Python
records["Customer_Clean"] = (
    records["Customer"]
    .str.strip()                 # =TRIM
    .str.replace(r"\s+", " ", regex=True)   # TRIM also collapses internal runs
    .str.title()                 # =PROPER
)

records["Postcode_Norm"] = records["Postcode"].str.upper().str.replace(" ", "", regex=False)
print(records[["Customer", "Customer_Clean", "Postcode", "Postcode_Norm"]])

str.strip() alone removes leading and trailing whitespace; Excel's TRIM also collapses runs of internal spaces, which is the second call above. Reproducing both is worth the extra line because "Acme Ltd" and "Acme Ltd" are different keys to every join and group-by that follows.

str.title() is close to PROPER but not identical — it capitalises after any non-letter, so "o'brien" becomes "O'Brien" in both, while "acme (uk) ltd" differs in how the parenthesised part is handled. For customer names, a lookup of known spellings usually beats any case function.

FIND, SEARCH and SUBSTITUTE

Python
# =IFERROR(FIND("-", A2), 0) — position of a substring
records["Dash_At"] = records["Reference"].str.find("-")

# =SEARCH is case-insensitive; the pandas equivalent is a case-insensitive regex
records["Has_Inv"] = records["Reference"].str.contains("inv", case=False, na=False)

# =SUBSTITUTE(A2, "-", "/")
records["Slashed"] = records["Reference"].str.replace("-", "/", regex=False)

# Extract with a pattern — no Excel equivalent short of nested FIND calls
extracted = records["Reference"].str.extract(r"^(?P<kind>[A-Z]{3})-(?P<year>\d{4})-(?P<serial>\d+)$")
print(extracted)

str.extract with named groups is the function that has no spreadsheet counterpart and replaces the largest amount of formula. A reference format that would take three nested FIND and MID calls to decompose becomes one pattern that either matches or produces NaN — and the NaN tells you which rows do not follow the format, which the formula version never would.

Splitting an email or an address into parts

A practical composite: pull the domain out of an email address, and flag the rows where the address does not look like one.

Python
records["Domain"] = records["Contact"].str.split("@").str[-1]
records["Valid_Email"] = records["Contact"].str.match(r"^[^@\s]+@[^@\s]+\.[^@\s]+$", na=False)

print(records.loc[~records["Valid_Email"], ["Reference", "Contact"]])

.str.split("@").str[-1] chains two string accessors — split produces a column of lists, and the second .str indexes into each list. That chaining is the idiom to reach for whenever a value needs decomposing and then selecting, and it stays vectorised throughout.

Numbers that have to become text

Report labels frequently mix numbers into strings, and that is where the translation of Excel's TEXT function is least direct. Excel's TEXT(A2, "#,##0.00") applies a number format and returns a string; pandas has formatting built into Python rather than into a function, which is more flexible and requires saying more.

Python
amounts = pd.Series([1200.5, 98000.0, 42.125])

# =TEXT(A2, "#,##0.00")
formatted = amounts.map(lambda v: f"{v:,.2f}")

# =TEXT(A2, "0.0%")
rates = pd.Series([0.0725, 0.128, 0.4])
percentages = rates.map(lambda v: f"{v:.1%}")

# =TEXT(A2, "0000") — zero-padded identifiers
codes = pd.Series([12, 407, 3])
padded = codes.astype("string").str.zfill(4)
print(formatted.tolist(), percentages.tolist(), padded.tolist())

str.zfill is the one worth knowing because it solves the leading-zero problem that Excel creates constantly: an account code stored as a number loses its zeros, and padding it back is a single call once you know the intended width.

A caution that applies to all three: producing a formatted string means the value is no longer a number, so it cannot be summed, sorted numerically or charted. When the destination is a spreadsheet, the better answer is nearly always to write the raw number and set the cell's number format, which keeps both the value and its appearance — the approach in Format Excel Cells as Currency with Python.

Cleaning a column before it becomes a key

Text functions are most often used in service of a join, and the failure mode is subtle: two columns that look identical on screen but differ in an invisible character. Non-breaking spaces from a web export, zero-width characters from a copy-paste, and trailing tabs all survive a visual inspection.

Python
import unicodedata

def normalise_key(series: pd.Series) -> pd.Series:
    return (
        series.astype("string")
              .map(lambda v: unicodedata.normalize("NFKC", v) if pd.notna(v) else v)
              .str.replace(r"[ ​\t]", " ", regex=True)
              .str.replace(r"\s+", " ", regex=True)
              .str.strip()
              .str.casefold()
    )

records["Key"] = normalise_key(records["Customer"])

NFKC normalisation folds the compatibility characters that cause most of these problems — a full-width letter, a ligature, a non-breaking space — into their ordinary equivalents. Applying the same function to both sides of a join is what makes it reliable; applying it to one side makes the mismatch worse.

Common pitfalls

SymptomCauseFix
AttributeError: Can only use .str accessor with string valuesThe column is numeric or object-typed.astype("string") first
Concatenation produces NaN for some rows+ propagates missing valuesUse str.cat(..., na_rep="")
Numbers become 1200.0 when concatenatedFloat formatting during conversionCast to Int64 before astype("string")
str.replace does nothingThe pattern is treated as a regex and contains special charactersPass regex=False for literal replacements
A join finds no matches after cleaningOnly one side was normalisedApply the same cleaning to both sides
str.contains raises on missing valuesNaN in the columnPass na=False

Performance and scale

Relative cost of the same extraction over a million rows An apply with a lambda pays a Python call per row, a regular expression is cheaper, a literal replacement cheaper still, and vectorised slicing is the fastest of the four. apply with a lambda a call per row regex replace engine per row literal replace regex=False slicing vectorised relative cost strings are the slowest columns; the method still matters

String operations are the slowest column operations in pandas, because strings are Python objects rather than packed numbers. Three choices make a large difference. Prefer regex=False when the pattern is a literal — the regex engine is a real cost. Chain methods rather than calling apply with a lambda, which pays a Python call per row. And convert a column with few distinct values to category before joining or grouping on it.

Python
# Slower: a Python call per row
records["Doc_Type"] = records["Reference"].apply(lambda ref: ref[:3])

# Faster: vectorised slicing
records["Doc_Type"] = records["Reference"].str[:3]

Where a text column drives repeated grouping — a region, a status, a document type — the categorical conversion pays for itself immediately, and it also shrinks the memory the frame occupies, which matters on the large exports covered in Working with Large Excel Files in Python.

Conclusion

LEFT, RIGHT and MID are all .str[a:b]; CONCATENATE is + and TEXTJOIN is str.cat with a placeholder; TRIM needs both strip and a whitespace collapse to match Excel exactly. Beyond the direct translations, str.split(expand=True) is Text to Columns in a line and str.extract with named groups replaces whole stacks of nested FIND and MID formulas — while telling you which rows do not fit the pattern.

Frequently asked questions

Why is .str0:3 used instead of a LEFT function? Because a pandas string column supports Python slicing directly, and slicing expresses LEFT, RIGHT and MID with one syntax instead of three functions. .str:3 is LEFT, .str-3: is RIGHT, and .str2:5 is MID with a start and length.

What is the difference between + and str.cat for joining columns? Plus produces NaN if any part is missing, exactly as CONCATENATE would produce an error. str.cat with na_rep supplies a placeholder instead, which is usually what a report needs.

How do I split a column into several, like Text to Columns? str.split with expand=True returns a DataFrame of the parts, which you can assign to several columns at once. Pass n to limit the number of splits when the last part may itself contain the separator.

Do these methods work on a numeric column? No — .str requires a string dtype. Convert first with .astype('string'), and be aware that a float column converts to values like '1200.0' rather than '1200', so cast to a nullable integer type before converting if that matters.