Excel TEXTSPLIT function

Function              TEXTSPLIT

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.

Remarks If there is more than one delimiter, then an array constant must be used.

For example, to split by both a comma and a period, use =TEXTSPLIT(A1,{“,”,”.”}).

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