How to Split Text in Excel (Text to Columns, TEXTSPLIT, and More)

Splitting text — breaking "John Smith" into separate first and last name columns, or splitting "Chicago, IL, 60601" into city, state, and zip — is one of the most common data cleanup tasks in Excel. Here are the four approaches, from fastest to most flexible.

Method 1: Text to Columns (fastest, one-time)

Best for: splitting a column of data once, using a delimiter like a comma, space, or tab.

Step 1. Select the column you want to split.

Step 2. Go to Data → Text to Columns.

Step 3. Choose Delimited, click Next.

Step 4. Check your delimiter (Comma, Space, or enter a custom one like / or |).

Step 5. Set the destination cell, click Finish.

This splits the data in-place and is permanent — it doesn't stay live if the source data changes.

Method 2: TEXTSPLIT (Microsoft 365 — live formula)

TEXTSPLIT splits text using a delimiter and spills the results into adjacent cells:

Splits A2 on spaces. For a comma-and-space delimiter:

Split into rows instead of columns (third argument):

TEXTSPLIT is live — if A2 changes, the split updates automatically.

Method 3: LEFT, MID, RIGHT (formula-based, precise)

When the text has a fixed structure (e.g., a 3-letter code always followed by a dash and a number), extract specific characters:

LEFT — extract from the beginning:

Returns the first 3 characters.

RIGHT — extract from the end:

Returns the last 5 characters.

MID — extract from the middle:

Returns 3 characters starting at position 5.

Finding the split point dynamically with FIND:

To split "First Last" where the last name starts after the first space:

Method 4: Flash Fill (no formula, pattern recognition)

Type the desired output in the column next to your data, then press Ctrl+E. Excel detects the pattern and fills the rest.

Works great for: extracting first names from "First Last", reformatting phone numbers, pulling domain names from email addresses. Doesn't stay live — it's a one-time fill.

Choose the method by data shape

Use Text to Columns when the delimiter is stable and you need a one-time split. Use TEXTSPLIT when the result should update as the source changes and your Excel version supports dynamic arrays. Use Flash Fill when the pattern is easy to demonstrate but difficult to express as a formula. For irregular addresses or repeated imports, Power Query is usually easier to refresh and audit.

Always preserve the original column until the result has been checked. Look for delimiters inside quoted text, missing values, suffixes, and rows with a different number of parts. A split that looks correct for the first five rows can still shift fields for one unusual record.

The Griddy way

Splitting text with inconsistent formatting — names with middle initials, addresses that sometimes have apartment numbers, IDs with varying lengths — is tedious to do with formulas. Just describe the structure:

"Split the full address column into separate Street, City, State, and ZIP columns. Some addresses have a suite number after the street address."

Griddy handles the edge cases and applies the logic to the whole column.

Sources

• Microsoft Support: TEXTSPLIT function
• Microsoft Support: Using Flash Fill in Excel