This post explains how to use the new TEXTSPLIT() function in Excel on a Windows PC. TEXTSPLIT() is a Text function in Excel. The purpose of this function is to split a given text string on the basis of one or more delimiters. The text is split into a dynamic array and the array values automatically spill into multiple cells across columns and/or rows. This is a useful function to split text strings that contain multiple spaces, commas, or a mix of different delimiters.
The syntax of the TEXTSPLIT() function is as follows:
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])
Where,
- text is the string that you want to split. This is a required argument. Can take a string or a cell reference.
- col_delimiter one or more characters that specify where to split the text. The resultant array is spilled across columns. This is also a required argument.
- row_delimiter one or more characters that specify where to split the text if col_delimiter is not specified. The resultant array is spilled across rows.
- ignore_empty takes either FALSE (default) or TRUE. By default, the function leaves empty cells between consecutive delimiters. If the ignore_empty argument is set to TRUE, no empty cells are created for two or more consecutive delimiters.
- match_mode takes either 0 (default) or 1. By default the delimiter search is case-sensitive. ‘1’ indicates that the search should be case-insensitive.
- pad_with argument takes the value that needs to be replaced with the missing values in a two-dimensional array. By default, the missing values show the #N/A error.
How to use the new TEXTSPLIT() function in Excel
The TEXTSPLIT() function is a new feature that is available only in Excel for Microsoft 365 and Excel for the web. If you have other editions of MS Excel, you can use the Text to Column wizard to split text in Excel.
Let us now see how to use the new TEXTSPLIT() function in Excel for the web.
Example 1: Using the col_delimiter argument
- In a new blank workbook, type ‘The Windows Club’ in cell A1.
- In cell B1, type the following formula:
=TEXTSPLIT(A1," ") - Press the Enter key.
Excel will split the text string on the basis of space ( ) delimiter. The output array will hold the following 3 values: ‘The’, ‘Windows’, and ‘Club’. These values will automatically be spilled across columns, starting from cell B1 (where the formula has been entered) through cell D1, as shown in the above image.
Example 2: Using the row_delimiter argument
- In the same workbook, type ‘The Windows Club. Owner – Anand Khanse.’ in cell A2.
- In cell B2, type the following formula:
=TEXTSPLIT(A2,,{".","-"}) - Press the Enter key.
Excel will split the text string on the basis of 2 delimiters here: full stop (.) and hyphen (-). The output array will hold the following 3 values: ‘The Windows Club’, ‘Owner’, and ‘Anand Khanse’. Since col_delimiter has been omitted in the formula, the output values will be spilled across rows, in cells B2, B3, and B4.
Example 3: Using the ignore_empty argument
- In the same workbook, type the following text in cell A1: ‘The..Windows..Club’
- Enter the following formula in cell B1:
=TEXTSPLIT(A1,".") - Press the Enter key.
The above formula will split the text string and the resultant array will be spilled horizontally across columns, as shown in the above image. Notice that an empty cell is created between the resultant values since the ignore_empty argument is not specified. So Excel is taking its default value which is False.
Now enter the following formula in cell B1: =TEXTSPLIT(A1,".",,TRUE)
As you can see, the empty cell has now been removed, since ignore_empty is set to TRUE.
Example 4: Using the match_mode argument
- Type ‘The x Windows x Club’ in cell A1 of the same workbook.
- In cell B1, type the following formula:
=TEXTSPLIT(A1,"X",,,1)
This will split the text string regardless of the case of the delimiter (x). If match_mode is not defined, the function will take its default value (0) and apply case sensitivity on the delimiter. So it won’t split the text, as shown in the above image.
Example 5: Using the pad_with argument
- In cell A1, enter the following text string: ‘The=1.Windows.Club=3’.
- In cell B1, enter the following formula:
=(TEXTSPLIT(A1,"=","."))
Since both col_delimiter and row_delimite are specified, the function will return a 2-dimentional array. The cell that has a missing value will show the ‘#N/A’ error. To remove this error, use the pad_with argument as follows:
=(TEXTSPLIT(A1,"=",".",,,"Not Specified"))
This formula will replace the #N/A error with the ‘Not Specified‘ text, as shown in the above image.
This is all about the new TEXTSPLIT() function in Excel. Hope you find this useful.
Why is Textsplit not working in Excel?
The TEXTSPLIT() function is currently available for cloud-based versions of Microsoft Office. So it will work in Excel for Microsoft 365 and Excel for the web. If you’re trying to use this function on the desktop version of Excel, you would probably see the #NAME? error, implying that this formula is invalid.
What is Textsplit?
TEXTSPLIT() is a text function in Excel that splits a given text string by using row or column delimiters. It stores the result in a one or two-dimensional array and then spills those array values across rows and/or columns, as specified by the user.