Text splitting

The TEXTJOIN function is one of the newer ones, until Office 2019 exclusively available to Microsoft 365 subscribers, and it allows joining multiple strings into one by specifying them and then defining delimiters and the option to ignore those that are empty. Since august, the TEXTSPLIT function appeared, and its purpose is to split strings. How to use this function you will find out in the “recipe” that follows…

The syntax of the TEXTSPLIT function is:

TEXTSPLIT (<text>, <delimiter>, [<row delimiter>], [<ignore blanks>], [<mode>], [<pad width>])

The first two arguments of the function are the text and the delimiter – the character which is used to separate the text. The third argument is optional and is entered if there is a delimiter to separate the text into multiple rows. The ignore-empty argument, as in the TEXTJOIN function, specifies whether to ignore empty strings. Mode indicates whether, when searching, the function is case sensitive. The last argument is a value to replace non-existent characters within two-dimensional arrays. Simply, if we specify a space as this argument, after the function returns an N/A error, the given string will be replaced with spaces.

For example, if we enter the formula:

=TEXTSPLIT(A1,” “)

function returns a dynamic string that the sentence “An apple a day keeps the doctor away. Far away.” separates according to columns so that each word occupies one cell.

Let’s write a slightly more complex formula:

=TEXTSPLIT(A1,” “,”.“,TRUE,0,” “)

The result is a sentence divided into two lines. Non-existent values are replaced with a space string.