Welcome to Excel Jet Consult blog post. In this tutorial, we are going to look at the brand new TEXTSPLIT, TEXTAFTER & TEXTBEFORE functions. Let’s get started.
Video
TEXTSPLIT Function
On March 16th 2022, Microsoft Excel Team released 14 brand new functions which you can read here.
The TEXTSPLIT Function splits text strings by using column and row delimiters.
The TEXTSPLIT function works the same as the Text-to-Columns wizard, but in formula form, and allows you to split across columns, or down by rows. It is the inverse of the TEXTJOIN function.
Syntax
=TEXTSPLIT(Input_Text, [col_delimiter], [row_delimiter], [Ignore_Empty])
The TEXTSPLIT function syntax has the following arguments:
- Input_Text The text you want to split. Required.
- [col_delimiter] One or more characters that specify where to spill the text across columns. Optional.
- [row_delimiter] One or more characters that specify where to spill the text down rows. Optional.
-
[Ignore_Empty] Specify TRUE to create an empty cell when two delimiters are consecutive. Defaults to FALSE, which means don’t create an empty cell. Optional.
In the caption below, we have dataset that is in column A which we want to split to columns B to E respectively
To split the text, in cell B1, execute the formula: =TEXTSPLIT(A1,” “)
Click Enter. From the caption below, we can see that the dataset is splitted into columns B to E respectively
We can of course split text into rows. As we can see from the caption below, we used the TEXTSPLIT Function to split the data in cell G1 to rows 3 to 10 using the space delimiter
TEXTAFTER Function
The TEXTAFTER Function returns a string of text that occurs after a substring in a string.
Syntax
=TEXTAFTER(input_text, text_after, [n], [ignore_case])
The TEXTAFTER function syntax has the following arguments:
- input_text The text you are searching within. Wildcard characters not allowed. Required.
- text_after The text that marks the point after which you want to extract. Required.
- [n] The nth instance of text_after that you want to extract. By default, n=1. A negative number starts searching input_text from the end. Optional.
-
[ignore_case] Specify FALSE to make the search case-sensitive. The default is TRUE which means the search is case-insensitive. Optional.
From the screenshot sample data above, we want to to return the substring, “Fabric Upholstered Stacking Chairs Rounded Back.”
To do that, in cell G3, execute the formula, =TEXTAFTER(G1,” “,H2). Note, we have 1 to 5 dropdown in cell H4 and we currntly choosed 2 from the dropdown.
Click Enter. From the screenshot below, we have the substring returned based on the delimiter and the instance number specified. The instance number in this case in the 2 which is coming from the data validation dropdown in cell H4. So, when we selected 3 from the dropdown, the formula updates accordingly
TEXTBEFORE Function
The TEXTBEFORE Function returns a string of text that occurs before a given substring in that string
Syntax
=TEXTBEFORE(input_text,text_before, [n], [ignore_case])
The TEXTBEFORE function syntax has the following arguments:
- input_text The text you are searching within. Wildcard characters not allowed. Required.
- text_before The text that marks the point before which you want to extract. Required.
- [n] The nth instance of text_before that you want to extract. By default, n=1. A negative number starts searching input_text from the end. Optional.
- [ignore_case] Specify FALSE to make the search case-sensitive. The default is TRUE which means the search is case-insensitive. Optional.
From the above caption, we want to return “Good Deluxe” using the TEXTBEFORE Function
To do that, in cell G5, execute the formula: =TEXTBEFORE(G1,” “,H4)
Click Enter. From the caption below, we can see that the formula worked pretty fine
This are awesome game-changer functions as far as text manipulation is concerned. In the next tutorial, we will consider how we can easily append dataset using the new VSTACK and the HSTACK Function. See you then