The syntax of the TEXTSPLIT() function is as follows: 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
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
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
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
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
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.
Is Textsplit function available in Excel?
Yes, TEXTSPLIT() function is available in Excel. 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. Read Next: Split CSV files with these free online tools and software.
