The output array will hold the following 3 values: ‘The’, ‘Windows’, and ‘Club’. In cell B1, type the following formula: =TEXTSPLIT(A1,' ')Įxcel will split the text string on the basis of space ( ) delimiter.In a new blank workbook, type ‘The Windows Club’ in cell A1.Example 1: Using the col_delimiter argument

Let us now see how to use the new TEXTSPLIT() function in 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. The TEXTSPLIT() function is a new feature that is available only in Excel for Microsoft 365 and Excel for the web. How to use the new TEXTSPLIT() function in Excel By default, the missing values show the #N/A error.

pad_with argument takes the value that needs to be replaced with the missing values in a two-dimensional array.

‘1’ indicates that the search should be case-insensitive. By default the delimiter search is case-sensitive.

match_mode takes either 0 (default) or 1.

If the ignore_empty argument is set to TRUE, no empty cells are created for two or more consecutive delimiters. By default, the function leaves empty cells between consecutive delimiters.

ignore_empty takes either FALSE (default) or TRUE.

The resultant array is spilled across rows.

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 columns.

col_delimiter one or more characters that specify where to split the text.

text is the string that you want to split.

The syntax of the TEXTSPLIT() function is as follows: =TEXTSPLIT(text,col_delimiter,, ) This is a useful function to split text strings that contain multiple spaces, commas, or a mix of different delimiters. The text is split into a dynamic array and the array values automatically spill into multiple cells across columns and/or rows. The purpose of this function is to split a given text string on the basis of one or more delimiters. This post explains how to use the new Excel TEXTSPLIT function.

Excel text to column formula