Excel TEXTSPLIT: split text by a comma, space or any character

TEXTSPLIT (Excel 365) splits text wherever a character appears, and spills the pieces into neighbouring cells. It is Text to Columns as a formula — it updates when the source changes.

The formula

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

Split by comma

=TEXTSPLIT(A2,",")

“red,green,blue” becomes three cells across: red | green | blue.

Comma followed by a space

“red, green, blue” leaves a space in front of each word. Split on both characters: =TEXTSPLIT(A2,", "), or wrap in TRIM: =TRIM(TEXTSPLIT(A2,",")).

Several possible separators

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

Splits on comma, semicolon or slash — useful for messy pasted data.

Down instead of across

=TEXTSPLIT(A2,,",")

Leave the column delimiter empty and give the row delimiter.

Skip empty pieces

“a,,b” gives an empty middle cell. Set ignore_empty to TRUE: =TEXTSPLIT(A2,",",,TRUE).

Just the first or last piece

• Before the first space: =TEXTBEFORE(A2," ")
• After the last space: =TEXTAFTER(A2," ",-1)

Common job: separate first and last name.

Older Excel

Use Data › Text to Columns (one-off) or Flash Fill.