Microsoft recently revealed not one, not two, but 14 new Excel functions, and more precisely new text and array functions for Excel 365.
These are going to help you save a lot of time making your spreadsheets more user-friendly, more flexible, and easier to use.
Now before moving on, please note that these functions are not yet available for everyone. Yeah, I know, it’s a shame! In fact, as we write these lines, these functions are currently available only to users running Microsoft 365 and on the Insiders Beta channel for Excel for Windows and Mac.
These 14 new functions are way too interesting to be covered in just a few words. Therefore, this post will focus on the text manipulation functions.
TABLE OF CONTENTS
Intro
Among the most common text manipulation functions used by every Excel user working with text, we had the FIND, LEFT, LEN, MID, RIGHT, SEARCH, and SUBSTITUTE functions.
But now we also have:
TEXTSPLIT: Splits text into rows or columns using delimiters.
TEXTBEFORE: Returns text that's before delimiting characters.
TEXTAFTER: Returns text that's after delimiting characters.
The three new functions allow you to dismember text strings with a minimum of effort in a very limited amount of time. What's not to like, huh?
Here is how they work.
The TEXTSPLIT function
By columns
The TEXTSPLIT function splits text strings using column and/or row delimiters. It is almost the opposite of the TEXTJOIN function. It has the following syntax:
TEXTSPLIT(text, [column delimiter], [row delimiter], [ignore empty], [pad with])
Let’s take a look at the different arguments:
text: This is required and represents the input text you want to split.
column delimiter: This is optional. It specifies where Excel should split the text into columns.
row delimiter: This row delimiter argument is also optional. It specifies where to spill the text down rows.
ignore empty: The optional ignore empty argument enables you to create an empty cell where two delimiters are consecutive by entering true. If you leave this blank, the formula will default to false.
pad with: this optional argument enables you enter text in place of blank results that would normally return an error.
Let’s jump right into an example that will explain things in the best way possible:
Here we have a list of names separated by a space delimiter. The old way would have been to use the LEFT function combined with the SEARCH function to split the first name out. The SEARCH function will find the space between the two names and return the character number to the LEFT function. Efficient, but not that easy. We can do better than that.
To find the last name, we could use the MID or RIGHT function combined with the SEARCH and LEN functions. Again, not that simple.
Too many functions involved, right? Let’s see what the TEXTSPLIT function can do in this situation.
Once the TEXTSPLIT function has been implemented, select your text in C6 and then you define your delimiter based on what you want the text to be split. Here it is based on a space delimiter placed between quotation marks.
=TEXTSPLIT(C6," ")
This is it! The text has been split into two different columns.
You can tell by the blue border around the cells that it spills into a dynamic array. It is the beauty of the new dynamic array functions. Each result is in its own cell, but the main function lives in the first one only.
By rows
We can also split down the rows and instead of the column delimiter, we’re going to enter a comma and then, specify a row delimiter by typing the space between quotation marks.
=TEXTSPLIT(C6,," ")
The more, the merrier: multiple delimiters
This text string has several delimiters we can use. How can we split it into different components? When we have multiple delimiters, we can wrap them in an array constant using the opening and closing curly brackets: {}.
=TEXTSPLIT(C6,{" ","|"})
Both ways: by rows and by columns
Here we have all this stuff in one cell. With the TEXTSPLIT function, we can split the input out by rows and columns using the following syntax:
=TEXTSPLIT(C3;": ";", ",FALSE,"")
The TEXTBEFORE function
The ins and outs
The TEXTBEFORE function returns the text that occurs before a given substring or delimiter. In case, there’s more than just one delimiter, the function TEXTBEFORE can return text before the nth instance of the delimiter.
TEXTBEFORE(text, delimiter, [instance number], [ignore case])
The TEXTBEFORE function has the following arguments:
text: the input text string to extract from.
delimiter: The character(s) that delimit the text and marks the point before which you want to extract.
instance number: This is the first optional argument and denotes the nth instance of the delimiter before which you wish to extract. By default, this is equal to one [1]. A negative number can be used here. In that case, the function will start searching for the delimiter from the end.
ignore case: This, too, is an optional argument and determines whether the search is case-sensitive or not. The default is FALSE, and it represents case sensitive search whereas TRUE makes the search case insensitive.
Here is an example:
If we want to extract just the first name, we can use the TEXTBEFORE function to make it happen in no time.
Once you have typed the TEXTBEFORE function in, select your text input and then specify the delimiter. Here it is the space delimiter again wrapped in quotation marks. Once it finds the first space, then it’s going to return everything before that first space.
=TEXTBEFORE(C6," ")
How many of them?
We can also specify an instance number here. If we want the function to return everything before the second space, then add 2 as a third argument.
=TEXTBEFORE(C6," ",2)
We get everything before the second space delimiter. It is very similar to the LEFT function except that it is more flexible because it allows you to define your delimiter.
Let’s have a look at this second example:
If we want to extract just the domain name, we can use the TEXTBEFORE function to make it happen using the following syntax:
=TEXTBEFORE(C6,"/",3)
We want this text split by the forward slash up to the third one.
The TEXTAFTER function
The ins and outs
The TEXTAFTER function is exactly like TEXTBEFORE except that it extracts a text from a string that occurs after the specified delimiter. It has the following syntax:
TEXTAFTER(text, delimiter, [instance number], [ignore case])
The TEXTAFTER function has the following arguments:
text: the input text string we are searching within.
delimiter: it marks the point after which you want to extract.
instance number: This is the first optional argument. If you have multiple delimiters and you want to skip one or more, you can enter the instance number. You can either enter a negative number here to search from the end of the input text or a positive to search from the beginning.
ignore case: This, too, is an optional argument and determines whether the search is case-sensitive or not. The default is FALSE, and it represents case sensitive search whereas TRUE makes the search case insensitive.
Here is an example:
If we want to extract the domain name from these URLs, we will, this time, use the TEXTAFTER function. The input text would be obviously the URL. Since we want the text after "www. ", then this would be the delimiter. There’s no need to add an instance here, because there’s only one instance of the delimiter.
=TEXTAFTER(C6,"www.")
In reverse mode
A second example using the instance argument in the TEXTAFTER function:
Here we want to extract the file extension with the following syntax using the reverse search with the -1 as the instance argument:
=TEXTAFTER(C6,".",-1)
The ‘dot’ delimiter (".") might exist multiple times in the file names. That’s why we want Excel to do a reverse search (from the right) to find the first dot and not take the other instances.
Conclusion
As you can see, these 3 functions have the potential to greatly simplify text operations in Excel. In many cases, they will be able to replace complex formulas combining multiple old text funcitons, making your spreadsheets simpler, easier to understand, and more dynamic.