Three New Excel Functions for Text Manipulation
Microsoft has just added 14 new functions that will make working with text and arrays more efficient.
Earlier this month, Microsoft announced that 14 new functions are being added to Excel. Full documentation can be found in Microsoft’s own TechCommunity. It is quite possible that at the time you are reading this post, the update hasn’t been released for the general public and is only available to Office Insiders — an early adopters’ MS Office community. In this post, I am going to provide information on joining this program and look into some of the new functions in more detail.
Joining Office Insiders
There are two levels of participation in the Office Insiders program:
- Beta level with the newest, but potentially buggy and unsupported features as soon as they are ready for Beta.
- Preview level with more stable and less frequent releases.
If you have a personal MS Office license like mine, you will need to open any Office application and do the following:
Help — > Check for Updates — > Advanced
From here, you will be able to select your participation level.
Instructions for the holders of other types of licenses are available on the Office Insider website. Now to the new functions!
This function allows splitting the text in a cell into multiple parts based on a delimiter defined by you. To make it even better, you can split your text across rows or columns, which makes the function more useful than the old way to split text (Data → Text to Columns).
The syntaxis of the function:
=TEXTSPLIT(Input_Text, [col_delimiter], [row_delimiter], [Ignore_Empty])
Input_Text is referring to the cell with your text.
Col_delimiter and row_delimiter are the symbols or combinations of symbols that Excel will use as delimiters to split into columns or rows. For our example let’s pretend we need to separate the values between colons. In order to split into columns, you will need something like (note the double commas):
=TEXTSPLIT(A2, “:”,, TRUE)
While splitting by rows will look like this:
=TEXTSPLIT(A2,, “:”, TRUE)
It is helpful to know that the delimiter itself will not be included in the resulting strings.
Ignore_Empty is the argument that determines whether a blank cell will be reserved when the string has two delimiters in a row — and it’s simply TRUE or FALSE. This argument is optional, and the setting is TRUE by default.
One more cool feature of the TEXTSPLIT() function is the ability to use more than one delimiter. If you want to split by columns the text where spaces, commas, and periods are used as delimiters, your function will look like this:
=TEXTSPLIT(A2, {“ ”, “,”, “.”})
Interestingly, since your Ignore_Empty is TRUE, all the cases where a space and a comma (or any other combination) were used together, will not result in empty output cells.
These two functions are very straightforward and similar. As the names imply, they allow extracting substring before and after a pre-defined point. They take up to four arguments, separated by a comma:
=TEXTBEFORE(input_text, text_before, [n], [ignore_case])
=TEXTAFTER(input_text, text_after, [n], [ignore_case])
Again, input_text is a cell reference, while text_before/text_after define the symbol or group of symbols, the text before or after which will be extracted by your function. These arguments are required.
Optional arguments have default values: n=1 and ignore_case is TRUE, which means that the string search is not case-sensitive (ignores case), and the first appearance of the before/after text is used.
I can see myself using these functions a lot instead of writing long nested formulas with LEN(), RIGHT(), LEFT() etc. Data cleaning in Excel, which is often a good choice for any datasets you receive as a spreadsheet, is going to be so much quicker.
Hopefully, I will have a chance to also dive into the new array manipulation functions and share what I learn— they look very exciting!
