One cell holding "Smith;John;Sales" needs splitting into columns. Use FIND to locate each delimiter and MID/LEFT to pull each piece — or TEXTSPLIT on Excel 365.
The example
"Smith;John;Sales" into 3 columns.
| A | B | |
|---|---|---|
| 1 | Field | Value |
| 2 | 1st | Smith |
| 3 | 2nd / 3rd | John / Sales |
The formula
The formula:
How it works
How it works:
- The first field:
LEFT(A2, FIND(";", A2) - 1)— everything before the first delimiter. - The last field:
TRIM(RIGHT(SUBSTITUTE(A2, ";", REPT(" ", 99)), 99))— the classic last-token trick. - Middle fields need MID with two FINDs, or the REPT/MID approach per position.
- On Excel 365,
TEXTSPLIT(A2, ";")spills all fields in one formula.
The REPT trick splits any token cleanly. SUBSTITUTE(text, delim, REPT(" ",99)) pads each delimiter to 99 spaces, then MID(…, (n-1)*99+1, 99) with TRIM grabs the n-th field. It handles variable-length pieces without nested FINDs — the pre-365 workhorse for splitting delimited text. On 365, just use TEXTSPLIT.
Try it: interactive demo
Delimited text and a delimiter.
Variations
Last field
REPT trick:
Nth field (REPT)
Any position:
365 spill
All at once:
Pitfalls & errors
Subtract 1 from FIND. LEFT length excludes the delimiter itself.
Missing delimiter errors. FIND fails if the delimiter isn’t present — wrap in IFERROR.
TEXTSPLIT is 365-only. Use the FIND/REPT approach in older Excel.
Practice workbook
Frequently asked questions
How do I split delimited text into columns with a formula?
How do I get the last field after a delimiter?
Is there a one-formula way?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class