TEXTSPLIT function. Split text into cells by specifying a delimiter.(Microsoft Excel)
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)
| Name | Omission | Explanation |
|---|---|---|
| text | Required argument. Specifies the text to be split. | |
| col_delimiter | Required argument. Specify a delimiter character. Two or more characters can be specified. This specification divides the cell into columns. | |
| row_delimiter | Specifies the delimiter character. Two or more characters may be specified. This specification divides the text into line feeds. | |
| ignore_empty | FALSE | Specifies whether to ignore empty elements when they occur. FALSE: Do not ignore. Creates an empty cell. TRUE: Ignore. Do not create empty cells. |
| match_mode | 0 | Specifies whether to be case-sensitive. 0: Case sensitive. 1: Not case sensitive. |
| pad_with | #N/A | Specifies the value of a cell that does not exist in the split result. |
Demonstrate
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.
---