TEXTSPLIT function. Split text into cells by specifying a delimiter.(Microsoft Excel)


Japanese version.

The TEXTSPLIT function is a new function added to Excel in September 2022.

This function is mainly used to split CSV and other data into cells.

Before this function was implemented, complex formulas using the IFERROR, MID, FIND, SUBSTITUTE, and LEN functions were required.
Alternatively, Text file wizard and VBA were often used.
However, with the advent of the TEXTSPLIT function, division is now possible with simple formulas.

How it works

=TEXTSPLIT(text,col_delimiter,row_delimiter,ignore_empty, match_mode, pad_with)
textRequired argument.
Specifies the text to be split.
col_delimiterRequired argument.
Specify a delimiter character.
Two or more characters can be specified.
This specification divides the cell into columns.
row_delimiterSpecifies the delimiter character.
Two or more characters may be specified.
This specification divides the text into line feeds.
ignore_emptyFALSESpecifies whether to ignore empty elements when they occur.
FALSE: Do not ignore. Creates an empty cell.
TRUE: Ignore. Do not create empty cells.
match_mode0Specifies whether to be case-sensitive.
0: Case sensitive.
1: Not case sensitive.
pad_with#N/ASpecifies the value of a cell that does not exist in the split result.


Basically, the first argument specifies the text to be split and the second argument specifies the delimiter. The result is a Spill.

The second argument can be two or more characters.

Argument 3:row_delimiter

The third argument specifies the delimiter character that divides in the line direction.

If you wish to leave it unspecified

Enter a series of commas. Nothing is specified for argument 3.

What to do if the last line is an error

If there is a newline character at the end of text, there will be an error line at the end.

To deal with this, specify TRUE for the fourth argument.

Or use the DROP function to delete the last row.

=DROP(TEXTSPLIT function,-1)

Which method is better depends on the text to be divided, so
Try both and choose the better one.
IGNORE_EMPTY may be inconvenient if there are empty cells in the normal range.

Argument 4:ignore_empty

If the delimiter occurs in succession, an empty cell is generated.
The fourth argument specifies whether to ignore it.

If omitted or FALSE, empty cells are created instead of ignored. This is more often the case in practice.

If TRUE is specified, it will be ignored and no empty cells will be created.

This specification is valid for both rows and columns.

Argument 5:match_mode

The fifth argument specifies whether the case is case-sensitive.

It can be 0 or omitted to split the case sensitive.

If 1 is specified, the division is made without distinction.

Argument 6:pad_with

If a row and column split is specified and the number of columns in the row is different, the

that cell will be filled with a #N/A error. Cell color yellow area.

Argument 6 can be a value to be filled in that case.



Microsoft Excel Functions Text