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(1A2,2" ")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | Ann | Berg | |
| 2 | Ann Berg |
In plain words: Split 1the full name in A2 into columns at 2each space.
B1=TEXTSPLIT("red,green,blue",",")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | red | green | blue |
B1=TEXTSPLIT("Ann:42;Ben:37",":",";")
| A | B | C | |
|---|---|---|---|
| 1 | Ann | 42 | |
| 2 | Ben | 37 |
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.