Excel TEXTSPLIT Function
On March 16, 2022, Microsoft announced 14 new Excel functions. Most of them are text functions. The new Excel TEXTSPLIT function is one the most useful functions. It is high time to learn about this function.
Summary
Before this function, users had to use Power Query or Mega formula made up of a combination of FIND, LEFT, RIGHT, and MID. Now the Excel TEXTSPLIT function has made the task much easier. The new excel TEXTSPLIT function splits text strings by using the column and row delimiters. This function works the same way as Text-to-Columns wizard, but in formula form, and allows users split across columns, or down by rows. It is the inverse of TEXTJOIN Function.
Syntax
=TEXTSPLIT(Input_Text, [col_delimiter], [row_delimiter], [Ignore_Empty])
where,
Input_text = the text or the cell reference of the text users want to split, and it is a required argument.
[col_delimiter] = One or more characters that specify where to spill the text across columns, and it is optional argument.
[row_delimiter] = One or more characters that specify where to spill the text down rows, and it is an optional argument.
[ignore_empty] = This is also an optional argument, in which users specify TRUE to create an empty cell when two delimiters are consecutive.
Example 1: We want to break apart the names in column B and put them into columns E and F.
Answer: The formula (in E5): =TEXTSPLIT (B5, ” “), which returns the new table we want.
Explanation of the formula:
=TEXTSPLIT(B5, ” “)
The first parameter is the text we want to split, which is in our example is B5. The second parameter is the delimiter that we want to use as a separator, which is space in our example and that has to go with a double quotes. Then press enter. You are done!
The magic of this formula is that it splits the contents of B5 automatically spilling into as many cells as it needs, and then users can copy the formula down.
Example 2: We want to split the telephone number into three columns using the hyphen (-) as a separator or delimiter.
Answer: The formula: =TEXTSPLIT(C5, “-“), which returns the table we want.
Explanation of the formula:
=TEXTSPLIT(C5, “-“)
C5 = reference to the text we want to split.
“-” = delimiter, where we want hyphen as delimiter.
The magic of this formula is that it splits the contents of C5 automatically spilling into as many cells as it needs, and then users can copy the formula down.
Example 3: Splitting text rows into columns: We want to split the names into two parts into two rows into E4 and E5.
Answer: The formula : =TEXTSPLIT(B5,, ” “), which returns the first and last names in the table we want.
Explanation of the formula in example:
- B5 = The text we want to split.
- ,, (two commas) = the first COMMA (,) is for argument, and the second COMMA is because we do not want column delimiter.
- ” “ (Space) = the row delimiter is the space within double quote.
Further reading:
Microsoft announced 14 new Excel Functions
More New Excel Functions:
There are some fascinating points in time on this article but I don’t know if I see all of them center to heart. There may be some validity but I will take hold opinion till I look into it further. Good article , thanks and we wish extra! Added to FeedBurner as well