Excel’s TEXTSPLIT function is a dynamic array formula that revolutionizes the way you split text strings.
In the past, this was a tedious and often manual task, requiring the “Text to Columns” wizard or a combination of formulas like LEFT, RIGHT, MID, and FIND.
Learning these functions to extract specific part of a text string was a rite of passage for many Excel user and often taught on excel training courses, as I was when I attended an excel course in London many years ago.
TEXTSPLIT, now available in Microsoft 365, simplifies this process by allowing you to split text into multiple cells with a single, dynamic formula.
The function is particularly powerful because it can handle multiple delimiters and split text both horizontally (into columns) and vertically (into rows) at the same time.
This makes it an essential tool for cleaning and organizing data imported from external sources like databases or text files, where information is often combined into a single cell.
How TEXTSPLIT Works: Syntax and Arguments
The basic syntax of the TEXTSPLIT function is straightforward:
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
- text (required)
This is the cell containing the text string you want to split apart.
- col_delimiter (required)
The character(s) that tell Excel where in the text to split the text into columns. You must use quotation marks (e.g., ,, ;, ).
- row_delimiter:
The character(s) that tell Excel where to split the text into rows.
- ignore_empty
A logical value (TRUE or FALSE) to specify whether to ignore consecutive delimiters. Defaults to FALSE.
- match_mode
A logical value (0 for case-sensitive, 1 for case-insensitive). Defaults to 0.
- pad_with
A value to fill any missing data points in a two-dimensional split. Defaults to #N/A.
When you use the function, it creates a dynamic array that “spills” the results into neighbouring cells.
Helpfully this then means that you only need to enter the formula once and Excel will automatically fill the required space, adjusting as your source text changes.
A Simple Example
Imagine you have a list of full names in a single column, and you want to separate them into two separate columns one called “First Name” and another called “Last Name”.
- Cell A1: John Smith
You can use the TEXTSPLIT function to do this in one simple step.
Formula: =TEXTSPLIT(A1,” “)
Explanation:
- A1: This is the text string we want to split.
- ” “: This is our column delimiter. We tell Excel to split the text whenever it encounters a space.
When you enter this formula in cell B1, the result will spill into two adjacent cells:
- Cell B1: John
- Cell C1: Smith
This shows how TEXTSPLIT eliminates the need for complex, nested formulas that used to be used to do the same thing.
An Advanced Example: Splitting with Multiple Delimiters
For a more advanced application, consider the type of data you might get from a simple export. A data set containing a whole series of products (product and product category) with each data item separated by a comma and with the product and category names separated by a semicolon.
- Cell A1: Electronics,Phones;Clothing,Shirts;Home Goods:Furniture
We want to split this data into a two-column table where the first column contains the product category and the second column contains the specific items.
Formula: =TEXTSPLIT(A1, “,”, “;”)
Explanation:
- A1 is the cell containing the source text string.
- “,”: This is the col_delimiter, telling Excel to split the text into columns wherever it sees a comma.
- “;”: This is the row_delimiter, telling Excel to split the text into new rows wherever it sees a semicolon.
When you enter this formula in a cell, the results will spill into a two-column, three-row array:
| B | C |
| Electronics | Phones |
| Clothing | Shirts |
| Home Goods | Furniture |
This single formula has transformed a messy, single-cell string into a clean, structured table, in one step.
This type of multi-delimiter, two-dimensional splitting is where the true power of TEXTSPLIT shines. Imagine having a dataset of thoughts of products!
If you’re a visual person you might want to consider this video.
Conclusion
The TEXTSPLIT function is a game-changer for anyone who works with text data in Excel. It represents a major leap forward in formula-based text manipulation, replacing old, clunky methods with a simple, dynamic, and incredibly powerful tool.
By enabling effortless splitting with multiple delimiters and into both rows and columns, it dramatically improves data cleanliness and productivity, making it an essential addition to any Excel user’s toolkit.
