Description
Use this function to split text strings into rows and columns based on specified delimiters.
Syntax
TEXTSPLIT(text, [col_delimiter], [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
text |
Yes | The text string or range to split. | A text string, a cell reference, or a cell range containing text. |
col_delimiter |
No | Character(s) to use as column separator. | A text string, which can be empty. If this is omitted, no column splitting occurs. |
row_delimiter |
No | Character(s) to use as row separator. | A text string, which can be empty. If this is omitted, no row splitting occurs. |
ignore_empty |
No | Whether to ignore empty elements in the result. Default is FALSE. | Only TRUE or FALSE (cannot use 1 or 0). |
match_mode |
No | Specifies how to match delimiters. Default is exact match (0). | 0 = Exact match, 1 = Case-insensitive. |
pad_with |
No | Value to use for padding if splits result in uneven rows. | Any value. If omitted, #N/A is used. |
Examples
Sample data
Data set 1
| A | B | |
|---|---|---|
| 1 | Juan,Doe,30,New York | Elaine,Sythic,24,Brooklyn |
| 2 | Jane,Smith,27,Chicago | Michael,Davis,34,Denver |
| 3 | Bob,Johnson,35,Los Angeles | Stavros,Kocic,27,Pittsburgh |
Data set 2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Day | Month | Year |
| 2 | 1/23/2024 | 1 | 23 | 2024 |
| 3 | 2/4/2023 | 2 | 4 | 2023 |
| 4 | 19/3/2025 | 19 | 3 | 2025 |
Data set 3
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Elaine Sythic,24,Brooklyn;Michael Davis,34,Denver;Stavros Kocic,27,Pittsburgh;LaShawn Smithson,29,Dallas | |||
| 2 | ||||
| 3 | ||||
| 4 |
Sample formulas
| Use case | Formula | Explanation and Result | ||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Split the text in a cell into multiple horizontally adjacent cells, using the comma as the separator. | =TEXTSPLIT(A1, ",") |
This formula splits the text in cell A1 into four horizontally adjacent cells, using the comma as the separator. For Data set 1, this formula returns the following:
Note: By default TEXTSPLIT treats empty values as blanks, so if the value in A1 had been Juan,Doe, ,New York (no age), this formula would return the following:
To ignore the empty value, set ignore_empty to TRUE:
|
||||||||||||||||||||||||||||||||||||||||
| Splits multiple rows of cells into vertically adjacent cells, using the comma as the separator. | =TEXTSPLIT(A1:B3, ",") |
This formula extracts the text in the cell array A1:A3 into vertically adjacent cells, using the first comma in each cell as the separator. For Data set 1, this formula returns the following:
|
||||||||||||||||||||||||||||||||||||||||
| Split a single cell with multiple values into 4 vertically adjacent cells. | =TEXTSPLIT(A1, , ",") |
This formula splits the text in cell A1 into four vertically adjacent cells, using the comma as the separator. For Data set 1, this formula returns the following:
|
||||||||||||||||||||||||||||||||||||||||
| Extract the third item from a data set in a cell. | =INDEX(TEXTSPLIT(A1, ","), 1, 3) |
This formula first splits the text in cell A1 into four cells using the comma as the separator. INDEX then extracts the third item. For Data set 1, this formula returns the following: 30 | ||||||||||||||||||||||||||||||||||||||||
| Split dates into day, month and year | =TEXTSPLIT(TEXT(A2, "m/d/yyyy"), "/") |
This formula first uses TEXT to change the value in cell A2 from a date into a text string with the format "m/d/yyyy". TEXTSPLIT then identifies the delimiter "/" for the 3rd (col_delimiter) argument. For Data set 2, this formula returns the following:
|
||||||||||||||||||||||||||||||||||||||||
| Take a mixed data string, separate out the entries, and sort them by city name. |
This use case requires 3 formulas in adjacent cells.
|
Using Data set 3, this creates the following: Formula 1 in cell B1 splits the string in A1 into vertical cells, creating a new cell at each ";".
Formula 2 (in cell C1) splits the string in B1 into vertical cells, creating a new cell at each "," producing the first row. Clicking and dragging the cells down fills out the next rows, resulting in:
Formula 3 (in cell F1) uses SORT to sort the cells in the range C1:E4 by the values in E1:E4 alphabetically by city, resulting in:
|
Notes
- As with all array functions, TEXTSPLIT dynamically populates results across adjacent cells, starting from the cell containing the TEXTSPLIT function.
- TEXTSPLIT:
- Can split both horizontally and vertically using different delimiters.
- Supports case-sensitive and case-insensitive matching.
- Can handle empty values with custom padding.
- If the provided delimiter is not found, TEXTSPLIT will return the original text unchanged. For example, if we use TEXTSPLIT on the text string "apple orange" with a period configured as the delimiter, TEXTSPLIT will return
apple orange.
Tips
- If you have header row(s), start your array values one row down (for example in the above data set, start with A2 rather than A1).
- TEXTSPLIT can be combined with other functions, including:
- VALUE: To convert text numbers to actual numbers.
- SORT: To arrange the results after the split (for example, to sort rows by surname after splitting a unitary given name-surname column.
- FILTER: To filter specific columns after splitting.
- UNIQUE: To retrieve unique values from split results.
- CONCAT: To recombine split results in new ways.
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.