How to Split Text in Excel (Text to Columns, TEXTSPLIT, and More)
Four ways to split text in Excel: Text to Columns wizard, TEXTSPLIT function, LEFT/MID/RIGHT formulas, and Flash Fill. The right method depends on whether you need it to stay live.
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:
=TEXTSPLIT(A2, " ")Splits A2 on spaces. For a comma-and-space delimiter:
=TEXTSPLIT(A2, ", ")Split into rows instead of columns (third argument):
=TEXTSPLIT(A2, , ",")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:
=LEFT(A2, 3)Returns the first 3 characters.
RIGHT — extract from the end:
=RIGHT(A2, 5)Returns the last 5 characters.
MID — extract from the middle:
=MID(A2, 5, 3)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:
=LEFT(A2, FIND(" ", A2)-1)=MID(A2, FIND(" ", A2)+1, LEN(A2))✦ TIP
Use FIND for case-sensitive searches and SEARCH for case-insensitive. Both return the position of a character within a string.
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.
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.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.