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.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
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.
Use this on real templates
Clean imported text before it hits your workflow
Text splitting is especially useful when imports arrive with combined names, addresses, notes, or IDs that need to be normalized first.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
Invoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Sales Pipeline
Track deals by stage, owner, value, and next move in one lightweight pipeline sheet. Keep close dates, weighted forecast, and rep follow-ups visible without needing a full CRM.