Split a Column into Multiple Columns with pandas

Exports love to cram several facts into one cell: Smith, Jane in a name column, UK-LON-0042 as a store code, 12 High Street, Leeds, LS1 4AB as an address, or red; blue; green as a list of tags. Excel's Data → Text to Columns wizard splits them once, by hand, and has to be repeated on every new file. pandas does the same split in a line that runs on every file automatically — and handles the awkward cases the wizard does not. This guide, part of Cleaning Excel Data With Pandas, covers each shape of split you are likely to meet.

Prerequisites

pip install pandas openpyxl

The examples use this sheet, written to customers.xlsx so every block can read it back:

import pandas as pd

pd.DataFrame({
    "Customer": ["Smith, Jane", "Okafor, Chidi", "de la Cruz, Ana", "Lee,Min"],
    "Store": ["UK-LON-0042", "NG-LOS-0007", "ES-MAD-0113", "KR-SEL-0021"],
    "Address": ["12 High Street, Leeds, LS1 4AB", "3 Marina Road, Lagos, 101241",
                "Calle Mayor 5, Madrid, 28013", "88 Jong-ro, Seoul, 03161"],
    "Tags": ["red; blue", "green", "blue; green; red", ""],
}).to_excel("customers.xlsx", index=False)

df = pd.read_excel("customers.xlsx", dtype={"Tags": str}).fillna({"Tags": ""})

Step 1 — Split on a delimiter into new columns

Series.str.split(sep, expand=True) returns a DataFrame with one column per piece. Assign its columns into the original frame by name, and strip whitespace from each piece, because a delimiter such as ", " is rarely applied consistently.

parts = df["Customer"].str.split(",", expand=True)
df["LastName"] = parts[0].str.strip()
df["FirstName"] = parts[1].str.strip()
print(df[["Customer", "LastName", "FirstName"]])

Splitting on "," rather than ", " and stripping afterwards is the robust habit: it handles Lee,Min with no space as well as Smith, Jane. Cleaning whitespace more generally — non-breaking spaces, doubled spaces — is covered in Strip Whitespace and Normalise Text Columns with pandas.

Step 2 — Cap the number of pieces with n and rsplit

An address such as 12 High Street, Leeds, LS1 4AB splits into three parts — until one arrives as Flat 2, 12 High Street, Leeds, LS1 4AB and produces four, shifting every column. Decide which end of the string is reliable and split from that end with a limit. Here the city and postcode are always the last two parts, so rsplit with n=2 keeps any extra commas in the street part.

address = df["Address"].str.rsplit(",", n=2, expand=True)
df[["Street", "City", "Postcode"]] = address.apply(lambda col: col.str.strip())
print(df[["Street", "City", "Postcode"]])

odd = pd.Series(["Flat 2, 12 High Street, Leeds, LS1 4AB"]).str.rsplit(",", n=2, expand=True)
print(odd.iloc[0].tolist())       # ['Flat 2, 12 High Street', ' Leeds', ' LS1 4AB']

split with n works the same way from the left. Without a limit, a single row with an extra delimiter adds a column to the whole result, and assigning three column names to four columns raises a length error — which is at least loud.

Step 3 — Extract parts with a regular expression

Codes with a fixed structure are better described than split. str.extract with named groups returns a DataFrame whose column names come from the pattern, and rows that do not match come back as missing values rather than silently wrong pieces.

store = df["Store"].str.extract(r"^(?P<Country>[A-Z]{2})-(?P<CityCode>[A-Z]{3})-(?P<StoreNo>\d{4})$")
df = df.join(store)
df["StoreNo"] = df["StoreNo"].astype("Int64")
print(df[["Store", "Country", "CityCode", "StoreNo"]])

bad = df["Store"].str.extract(r"^(?P<Country>[A-Z]{2})-")["Country"].isna()
print("codes that do not match:", df.loc[bad, "Store"].tolist())

The anchors ^ and $ make the pattern reject codes that merely contain a valid code somewhere inside them, and the named groups document the structure better than a comment would. Int64 turns the numeric part into a nullable integer, so a non-matching row stays <NA> instead of forcing the column to float.

Step 4 — Handle inconsistent delimiters

Hand-typed lists use whatever separator the person reached for: semicolons, commas, slashes, the word "and". A regular expression as the separator covers them all at once.

messy = pd.Series(["red; blue", "green/red", "blue , green and red", "red"])
tags = messy.str.split(r"\s*(?:[;,/]|\band\b)\s*", regex=True)
print(tags.tolist())
# [['red', 'blue'], ['green', 'red'], ['blue', 'green', 'red'], ['red']]

Without expand=True, split returns a list per row, which is the right shape when rows hold different numbers of values — the next step turns those lists into rows. Passing regex=True explicitly avoids ambiguity: pandas otherwise treats a separator longer than one character as a regex, and a single character as a literal.

Step 5 — Turn multi-value cells into rows

A Tags column with blue; green; red in one cell is really three facts. Split into lists, then explode gives each tag its own row with the rest of the record repeated, which is the shape counting, filtering and pivoting need.

tag_rows = (
    df[["Customer", "Tags"]]
      .assign(Tag=df["Tags"].str.split(r"\s*;\s*", regex=True))
      .explode("Tag")
      .query("Tag != ''")
      .drop(columns="Tags")
      .reset_index(drop=True)
)
print(tag_rows)
print(tag_rows["Tag"].value_counts())

The query removes the empty tag produced by customers with no tags at all. To go the other way — one column per possible tag with a true/false flag — use df["Tags"].str.get_dummies(sep="; "), which is often the most convenient input for a summary pivot like those in Create a Pivot Table from Excel with Pandas.

Step 6 — Write the split columns back next to the original

When the result goes back to Excel for people, place the new columns beside the source column rather than at the far right, so the sheet reads naturally. Build the column order explicitly and write once.

order = ["Customer", "LastName", "FirstName", "Store", "Country", "CityCode", "StoreNo",
         "Address", "Street", "City", "Postcode", "Tags"]
df[order].to_excel("customers_split.xlsx", index=False)
print(pd.read_excel("customers_split.xlsx").columns.tolist())

Keeping the original column alongside the parts makes the split auditable: a reviewer can see de la Cruz, Ana next to de la Cruz and Ana and spot a bad rule at a glance. Drop it only once the rule has been checked on real data.

Step 7 — Check the split with a round trip

The strongest test of a split rule is that the pieces can rebuild the original. Join the parts with the same delimiter, compare with the normalised source, and list every row where they differ. Rows that fail the round trip are exactly the ones the rule does not understand — an extra comma, a missing part, a delimiter nobody expected.

def round_trip_failures(frame: pd.DataFrame, source: str, parts: list[str], sep: str) -> pd.DataFrame:
    rebuilt = frame[parts].astype("string").fillna("").agg(sep.join, axis=1)
    normalised = frame[source].astype("string").str.replace(r"\s*,\s*", sep, regex=True).str.strip()
    return frame.loc[rebuilt != normalised, [source, *parts]]

print(round_trip_failures(df, "Customer", ["LastName", "FirstName"], ", "))
print(round_trip_failures(df, "Address", ["Street", "City", "Postcode"], ", "))

Both checks print empty frames for this data. Run the same check on every new file before trusting the columns: a non-empty result is a short, specific list of rows to look at, which is far easier to act on than discovering weeks later that a city column contains postcodes for a few hundred customers. For structured codes, the equivalent check is simply counting the missing values that str.extract produced.

Common pitfalls

Performance and scale notes

The .str methods run in Python per element, which is fast enough for hundreds of thousands of rows but noticeably slower than numeric operations. str.split with expand=True and str.extract are comparable; explode is efficient but multiplies the row count, so filter before exploding when you only need some records. For very large string workloads, reading with pandas' Arrow-backed string dtype (dtype_backend="pyarrow") speeds up these operations considerably.

Conclusion

Replace Text to Columns with str.split(..., expand=True) and a strip, cap the pieces with n or split from the reliable end with rsplit, and describe structured codes with str.extract and named groups so mismatches show up as missing values. Use a regex separator for inconsistent lists, explode multi-value cells into rows, and write the parts back beside the original column so the rule can be checked.

Frequently asked questions

What is the pandas equivalent of Excel Text to Columns? Series.str.split(delimiter, expand=True), which returns one column per piece. Assign the result to new column names and strip whitespace from each.

How do I split a column only on the first delimiter? Pass n=1 to str.split. Everything after the first delimiter stays together in the second column. Use rsplit with n=1 to split on the last delimiter instead.

How do I split full names into first and last names? Split on the delimiter the data actually uses. For Last, First formats split on the comma; for First Last formats, rsplit on a space with n=1 so multi-part first names stay together, and review names with particles by hand.

Can I split a column in place with openpyxl instead? You can, by reading each cell, splitting the string and writing parts into neighbouring cells, but pandas is simpler whenever the whole column follows one rule.

Related

• Up one level: Cleaning Excel Data With Pandas — the full cleaning workflow.
• Excel Text Functions LEFT, RIGHT, MID and CONCAT in pandas — positional text extraction.
• Unpivot a Wide Excel Sheet with pandas melt — the column-wise cousin of explode.
• Convert Excel Text Columns to Numbers with pandas — typing the pieces you split out.
• Fuzzy Match Names Between Two Excel Files with Python — matching the names once they are split.