The TEXTSPLIT function

Splits text into pieces at a delimiter and spills them across columns, or down rows when you also give a row delimiter.

Syntax

TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
text
The text to split.
col_delimiter
The character or text that separates columns, such as " " or ",".
row_delimiter (optional)
The character or text that starts a new row.
ignore_empty (optional)
TRUE to skip empty pieces when two delimiters are next to each other. Left out, FALSE.
match_mode (optional)
1 to ignore upper and lower case in the delimiter. Left out, 0, which matches case.
pad_with (optional)
What to put in cells a short row leaves empty. Left out,

Examples

C1=TEXTSPLIT(A2," ")
ABCD
1Full nameAnnBerg
2Ann Berg

In plain words: Split the full name in A2 into columns at each space.

B1=TEXTSPLIT("red,green,blue",",")
ABCD
1redgreenblue
B1=TEXTSPLIT("Ann:42;Ben:37",":",";")
ABC
1Ann42
2Ben37

Read the guide

Questions

Why are the numbers from TEXTSPLIT not adding up?

TEXTSPLIT returns text, even for pieces that look like numbers. Wrap it in VALUE, or put -- in front, to turn them into numbers.

What can I use instead of TEXTSPLIT in older Excel or Google Sheets?

Google Sheets has SPLIT, which works much the same. In older Excel, use Data > Text to Columns, or take the pieces out with LEFT, MID and FIND.

Related functions