The Text to Columns button gives a one-time result. TEXTSPLIT is a formula, so when the source text changes, the split result changes too. It is available in Microsoft 365 and Excel 2021 or newer.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter])
Split a full name into two columns
If A2 holds "Priya Sharma":
=TEXTSPLIT(A2, " ")
The first name goes in the cell with the formula and the last name spills into the cell on the right.
Split by comma
If A2 has "Excel, Power BI, SQL":
=TEXTSPLIT(A2, ", ")
Split into rows instead of columns
Leave the column delimiter empty and give a row delimiter:
=TEXTSPLIT(A2, , ", ")
The skills now come down the column, one per row.
Several delimiters at once
Use an array of delimiters inside curly brackets: =TEXTSPLIT(A2, {",",";"}) splits on a comma or a semicolon.
Common mistakes
- Cells to the right must be empty or you get #SPILL!.
- Extra spaces after the comma stay in the result unless you include the space in the delimiter or wrap the formula in TRIM.
- In older Excel versions, use LEFT, MID and FIND or Text to Columns instead.