Split Text By Delimiter
Split text by delimiter in Excel and Google Sheets — use TEXTSPLIT, SPLIT, LEFT with FIND, and Text to Columns to break names, emails and codes into columns.
Introduction
Data often arrives crammed into a single cell — full names, address lines, or email addresses separated by a comma, space, or semicolon. Splitting that text by its delimiter turns one messy column into clean, analyzable columns. This guide covers the one-click modern functions (TEXTSPLIT and SPLIT), the classic formula approach, and Text to Columns.
Prerequisites
- Basic formula editing in Excel or Google Sheets
- Understanding of cell references
1Modern Way: TEXTSPLIT (Excel 365) and SPLIT (Google Sheets)
If you are on Excel 365 or using Google Sheets, splitting text is a one-formula job. Excel's TEXTSPLIT takes the text and the delimiter, then spills every part into neighboring cells. Google Sheets' SPLIT does the same with a slightly different syntax. Both accept multi-character delimiters, which older methods struggle with.
Enter the formula
In the cell where you want the first part to appear, reference the source cell and its delimiter.
=TEXTSPLIT(A1, ",")Google Sheets equivalent
Sheets uses SPLIT with the delimiter as the second argument.
=SPLIT(A1, ",")Example
=TEXTSPLIT(A1, ",")TEXTSPLIT(A1, ",") breaks the string at every comma and returns a horizontal array. Add a row delimiter as the second argument to split downward instead.
In Excel 365 the result spills automatically — make sure the cells to the right are empty.
In Google Sheets, wrap SPLIT in INDEX or ARRAYFORMULA if you need a single part rather than the whole row.
2Classic Formulas: LEFT + FIND for the First Part
In older Excel versions there is no TEXTSPLIT, but you can extract each piece with LEFT, RIGHT, and FIND. FIND returns the position of the delimiter, and LEFT grabs everything before it. This method works in every version of Excel and Google Sheets.
Find the delimiter position
Confirm where the delimiter sits with FIND — it is case-sensitive; use SEARCH if you need a case-insensitive match.
=FIND(",", A1)Extract everything before it
Use LEFT with the position minus one.
=LEFT(A1, FIND(",", A1) - 1)Extract everything after it
RIGHT plus LEN skips past the first delimiter and returns the remainder.
=RIGHT(A1, LEN(A1) - FIND(",", A1))Example
=LEFT(A1, FIND(",", A1) - 1)FIND locates the comma at position 5, so LEFT(A1, 4) returns the four characters before it. Subtracting 1 excludes the delimiter itself.
RIGHT(A1, LEN(A1) - FIND(",", A1)) on our example returns Smith,[email protected] — the rest of the string after the first comma.
If the delimiter might be missing, wrap the formula in IFERROR to return the original text instead of a #VALUE! error.
3Extract the Nth Part with SUBSTITUTE
When you need a specific piece — say the third token — the SUBSTITUTE trick turns the delimiter into a block of spaces, then MID reads the block you want. It looks cryptic but works in every Excel version and is fully self-contained.
Pad each token
Replace the delimiter with REPT(" ", 100) to give each part a fixed-width slot.
=SUBSTITUTE($A1, ",", REPT(" ", 100))Read slot n
MID starts at (n-1)*100+1 and reads 100 characters; TRIM strips the spaces. Change 2 to any part number.
=TRIM(MID(SUBSTITUTE($A1, ",", REPT(" ", 100)), (2-1)*100 + 1, 100))Example
=TRIM(MID(SUBSTITUTE($A1, ",", REPT(" ", 100)), (2-1)*100 + 1, 100))SUBSTITUTE replaces each comma with 100 spaces, so every token occupies its own 100-character slot. MID reads slot number n (here n=2), and TRIM removes the padding.
Use a width larger than your longest token — 100 covers almost everything; 250 is safer for long text.
This formula is a favorite for splitting domain names, SKU codes, and comma-separated tags.
4Text to Columns: The No-Formula Method
Excel's built-in Text to Columns wizard splits a selected range in a few clicks — ideal for one-off cleanups where you don't want formulas at all. It is destructive, though: the original column is overwritten, so work on a copy.
Select the column
Highlight the cells you want to split, then go to Data > Text to Columns.
Choose Delimited
Pick the Delimited option and click Next.
Pick the delimiter
Check Comma, Space, Semicolon, Tab, or type a custom character, then click Finish.
Example
Data > Text to Columns > Delimited > CommaThe wizard writes static values — if the source data changes you must re-run it, which is why formulas are usually better for recurring imports.
Google Sheets has an equivalent: Data > Split text to columns.
Make sure the columns to the right are empty or Excel will warn you about overwriting data.
5Related Cleanup: SUBSTITUTE, TEXT, and Joining Back
Splitting is often step one. SUBSTITUTE normalizes delimiters before splitting (for example, turning semicolons into commas), and TEXT formats numbers so they look right once separated. If you later need to reverse the operation, TEXTJOIN stitches the pieces back together.
Example
=SUBSTITUTE(A1, ";", ",")Normalizing every delimiter to one character lets a single splitting formula handle mixed input — run SUBSTITUTE first, then TEXTSPLIT or LEFT/FIND on the result.
TEXTSPLIT also accepts an ignore_empty argument to skip consecutive delimiters like ",,".
When formatting numbers as text before joining, use =TEXT(B1, "0.00") so 12.5 displays as 12.50.
Functions Used
TEXT
TEXT converts a number or date into text using a user-supplied format code, so the displayed value can be concatenated or presented in a custom format.
LEFT
LEFT returns the first character(s) from the beginning of a text string.
RIGHT
RIGHT returns the last character(s) from the end of a text string.
FIND
FIND returns the position of one text string inside another. It is case-sensitive.
SUBSTITUTE
SUBSTITUTE replaces one text string with another in a text string.
Related Guides
Extract Text From String
Extract text from string in Excel and Google Sheets using LEFT, RIGHT, MID, and FIND. Learn to pull the first or last word, a middle substring, or text between two delimiters.
CONCATENATE vs TEXTJOIN
Concatenate vs TEXTJOIN in Excel and Google Sheets: compare syntax, delimiters, and empty-cell handling to pick the right text-joining function every time.
Google Sheets ARRAYFORMULA
Google Sheets ARRAYFORMULA applies one formula to an entire range at once, so you never copy down again. Learn how to combine it with IF, FILTER, and QUERY.
Summary
Splitting text by a delimiter is easy in Excel 365 and Google Sheets with TEXTSPLIT and SPLIT, which spill every part into neighboring cells automatically. On older Excel, LEFT + FIND extracts the first part, RIGHT + LEN grabs the remainder, and the SUBSTITUTE + MID trick pulls out any nth token. For one-off cleanups, Text to Columns does the job without formulas — just remember it overwrites the source and must be re-run when data changes.
Next Steps
- Apply the SUBSTITUTE + MID formula to a column of SKU codes to isolate the product-code segment
- Try TEXTSPLIT with two delimiters — a comma for columns and a semicolon for rows
- Learn TEXTJOIN to reverse the process and reassemble split parts into one cell