TEXTSPLIT function
Quick answer
TEXTSPLIT splits text into separate cells at a delimiter: across columns, down rows, or both.
- Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])- Most common use
=TEXTSPLIT(A2,", ")returns Okafor (Splits at comma-space into two columns)
Use it to break "Okafor, Ada" into last and first name, split a list of tags, or turn pasted comma-separated text into a table. It spills the pieces, so one formula fills as many cells as needed.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Text functions
- Returns
- An array of text pieces that spills across, and down when there is a row delimiter.
- Google Sheets
- In Google Sheets, SPLIT does this job.
Syntax
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text to split. |
col_delimiter | Required | The text that separates columns, such as ","; or several in braces, like {",",";"}. |
row_delimiter | Optional | Optional: the text that separates rows. |
ignore_empty | Optional | Optional: TRUE to skip empty pieces between two delimiters. FALSE if omitted. |
match_mode | Optional | Optional: 1 to match the delimiters without regard to case. 0 if omitted. |
pad_with | Optional | Optional: what fills short rows when splitting into a table; #N/A if omitted. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| B2 | =TEXTSPLIT(A2,", ") | Okaforspills 1 × 2 | Splits at comma-space into two columns |
| B3 | =TEXTSPLIT(A3,", ") | Lindqvistspills 1 × 2 | Same split for the next name |
| E3 | =TEXTSPLIT(E2,";",,TRUE) | redspills 1 × 3 | The empty piece between ;; is skipped |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | Last | First | Tags | |||
| Okafor, Ada | Okafor | Ada | red;blue;;green | |||
| Lindqvist, Erik | Lindqvist | Erik | red | blue | green |
Practice TEXTSPLIT
Graded instantly, then re-checked on changed data.
Split SKUs
A2:A6 has three parts separated by hyphens, like CHR-BLU-42. In B2, split the SKU into its three parts across B2:D2, and fill down to row 6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #SPILL! | Cells to the right (or below) aren't empty, so the pieces have nowhere to go. |
| Extra spaces in the pieces | Split on ", " (comma and space) rather than ",", or wrap the result in TRIM. |
| #N/A in some cells | When splitting into rows and columns, short rows are padded with #N/A. Give pad_with a value, such as "". |
Tips
- Split on several delimiters at once with an array: =TEXTSPLIT(A2,{",",";"}).
Questions
How do I split text by a comma in Excel?
=TEXTSPLIT(A2,",") spills each piece into its own column. Without Microsoft 365 or Excel 2024, use Data > Text to Columns.
How do I split text into rows instead of columns?
Leave col_delimiter empty and give row_delimiter: =TEXTSPLIT(A2,,",").
Answers that use TEXTSPLIT
Updated Checked by running: every formula on this page was run by our formula engine on .