TEXTSPLIT in Excel: Split Text Into Columns

TEXTSPLIT in Excel splits the text in one cell into several cells at a delimiter, such as a comma or a space, with a single formula. Type =TEXTSPLIT(A2,",") and each item spills into its own column. This guide is for anyone who has pasted in comma-separated lists, tags or full names and needs them in separate cells, and it covers every argument with examples you can type in.

What does TEXTSPLIT do in Excel?#

TEXTSPLIT cuts text at every delimiter and returns the pieces as an array that spills into neighbouring cells. A delimiter is the character that separates the parts, like the comma in red,green,blue. A spill means one formula fills several cells: you type it in one cell, and Excel writes the results into the cells beside it.

Version note: TEXTSPLIT is available in Microsoft 365, Excel 2024 and Excel for the web. In Excel 2021, 2019 and older, it shows #NAME?. The last section covers what to use there.

TEXTSPLIT syntax#

Only the first two arguments are needed for most jobs.

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

How to split text into columns with TEXTSPLIT#

Give TEXTSPLIT the cell and the delimiter, and it does the rest. Here are some orders whose items arrived in one cell:

In B2, type:

=TEXTSPLIT(A2,",")

"Desk lamp" lands in B2, and "USB-C hub" and "Notebook" spill into C2 and D2. To do the other rows, select B2:B4 only (not columns C and D) and fill down with Ctrl+D on Windows or ⌘D on a Mac. Each row spills as wide as it needs.

If the items are separated by a comma and a space, use both as the delimiter: =TEXTSPLIT(A2,", "). Otherwise every item after the first starts with a space.

How to split text into rows instead of columns#

Leave the column delimiter empty and put the delimiter in the third argument to split down a column:

=TEXTSPLIT(A2,,",")

The two commas in a row mean "no column delimiter". Now each item goes in a new row, which is easier to read for long lists.

You can use both at once to turn one cell into a small table. With a=1;b=2;c=3 in A2:

=TEXTSPLIT(A2,"=",";")

Each ; starts a new row and each = starts a new column, giving three rows of two cells: a and 1, b and 2, c and 3. The numbers come back as text, so wrap the result in VALUE, or multiply by 1, before you add them up.

How to split at several different delimiters#

Put the delimiters in braces to split at any of them. Braces make an array constant, a fixed list typed into the formula:

=TEXTSPLIT(A2,{",",";"})

For red;green,blue, this gives red, green and blue in three cells. It is much shorter than nesting SUBSTITUTE to turn every separator into one before splitting.

How to handle empty values and uneven rows#

Set ignore_empty to TRUE when the text has doubled delimiters. In red,green,,blue there is nothing between the second and third comma:

When you split into rows and columns and some rows are shorter, Excel fills the gaps with #N/A. Use pad_with to show something friendlier. =TEXTSPLIT(A2,"=",";",,,"-") fills the gaps with a dash.

The match_mode argument matters only when your delimiter is a letter. =TEXTSPLIT(A2,"x",,,1) splits at both "x" and "X".

How to get one item from the split#

Wrap TEXTSPLIT in INDEX to return a single piece instead of the whole spill. To get the second item:

=INDEX(TEXTSPLIT(A2,","),2)

For the first or last item around one delimiter, TEXTBEFORE and TEXTAFTER are often simpler, because they return one result and don't spill. To count how many items a cell holds, =COUNTA(TEXTSPLIT(A2,",")) works, or see our guide to counting characters and words in a cell.

TEXTSPLIT also feeds other dynamic array functions. =SORT(TEXTSPLIT(A2,,",")) splits a list into rows and sorts it A to Z. The guide to SORT and SORTBY explains the sorting options.

Common TEXTSPLIT mistakes#

Most problems come from the spill or the delimiter:

• #SPILL! The cells to the right aren't empty. Clear them. If you filled the formula across columns C and D as well, delete those copies.
• Leading spaces. You split at "," but the text uses ", ". Include the space in the delimiter, or wrap the result in TRIM.
• Numbers that won't add. Split pieces are always text. Use =VALUE(...) or =TEXTSPLIT(A2,",")*1.
• Case-sensitive delimiters. Splitting at "x" ignores "X" unless you set match_mode to 1.
• Splitting a whole range in one formula. TEXTSPLIT works on one cell at a time. Write it in the first row and fill down.

Splitting names is a special case with its own traps, like middle names and "Last, First" order. Our guide on how to split first and last names covers them. And if two codes look the same but differ only in capitals after splitting, EXACT compares them case by case.

How to split text in older Excel#

Without TEXTSPLIT, you have two options. For a one-off job, use Data > Text to Columns: choose Delimited, tick the delimiter and click Finish. It splits the cells in place, with no formula, so it doesn't update when the data changes. The Text to Columns guide walks through each screen.

For a formula that works in any version, combine LEFT, MID and FIND. The first item before a comma is:

=LEFT(A2,FIND(",",A2)-1)

The LEFT function guide shows how LEFT counts characters, and LEFT, RIGHT and MID shows the full pattern for the middle and last pieces.

Practise it#

Try the live exercise above, then open the Modern Excel lesson, where you split names with TEXTSPLIT and fill it down without causing a spill error. For splitting at the first or last delimiter, the TEXTBEFORE and TEXTAFTER lesson lets you practise on emails and file paths in a live spreadsheet.