Lesson#159: What is Excel function TEXTSPLIT? TEXTSPLIT vs TEXTJOIN comparison.

Microsoft Excel has introduced some new functions in 2022. One of them is the Excel TEXTSPLIT function.

What is the TEXTSPLIT function?

This function works like the Text to Column feature in Excel. This feature splits texts across columns or down by rows.

It is an inverse function of the TEXTJOIN function.

Description        Excel TEXTSPLIT function splits text strings by using column and row delimiters.

Syntax  TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

text       The text you want to split. Required.

col_delimiter       The text that marks the point where to spill the text across columns.

row_delimiter       The text that marks the point where to spill the text down rows. Optional.

ignore_empty       Specify FALSE to create an empty cell when two delimiters are consecutive. Defaults to TRUE, which creates an empty cell. Optional.

match_mode    Searches the text for a delimiter match. By default, a case-sensitive match is done. Optional.

pad_with           The value with which to pad the result. The default is #N/A.

Example: 1

Here I am having this TEXT value in the A1 cell, “Welcome to Excelabcd – Blog to learn Excel”

Now I shall apply the TEXTSPLIT function to it.

I have kept the col_delimiter as ” “, which means <SPACE> and the row_delimiter as “-“

Now, this is the result. There is a #N/A error showing to keep the table format intact. Nothing to worry about it. I am coming to it in the next examples.

Now I have added value to one more argument, which is ignore_empty. This is by default 0 (FALSE), but I have added 1 (TRUE).

Now see it has ignored the empty values in the text.

Now you have seen examples of using col_delimiter, row_delimiter, and ignore_empty; these three arguments. Then let’s move to the next example.

Example: 2

Here I have another example of text, “10ftX14ft,45ftx23ft”. Note that one “X” is a large case and the other “x” is in a small case.

Now I shall discuss the fourth argument match_mode, I have kept the value 1 (TRUE).

See, it has split in both small and large case “x”.

What if I put the match_mode 0 (FALSE)

It will only split the small case “x” but not the large case “X” because in the row_delimiter I have put “x” in the small case. It is showing #N/A

Now, what does the fifth and last argument pad_with do? It just replaces the #N/A error with your desired values. I have put that “Not found”.

I hope you have understood the TEXTSPLIT function. Now I shall talk about the TEXTJOIN function.

What is the TEXTSPLIT function?

Description        Excel TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

Syntax  TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)

delimiter  Separator between each text.

ignore_empty  Whether to ignore empty cells or not.

text1  First text value or range.

text2  [optional] Second text value or range.

Example:

Here I am applying the TEXTJOIN function an array of text and number values. I have put the delimiter as “-” and 1 for ignore_empty.

Let’s keep one cell in the array empty.

And I shall put 0 in the place of ignore_empty.

This is including the empty cell also.

Now let’s put 1 in the place of ignore_empty

Now, this does not include empty cells.

TEXTSPLIT vs TEXTJOIN comparison

	TEXTSPLIT	TEXTJOIN
Category	TEXT function	TEXT function
Used for	Splits text based on delimiter across rows and columns	Joins texts with delimiter from rows or columns
Syntax	TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])	TEXTJOIN (delimiter, ignore_empty, text1, [text2], …)

What version of Excel is TEXTSPLIT?

Currently, TEXTSPLIT is available on Excel for Microsoft 365 (Windows), Excel for Microsoft 365 (Mac), and  Excel for the web only.

Join our Telegram channel https://t.me/excelabcd

Here you shall be regularly updated with Excel tips and tricks.