Excel Tips and Tricks #462 New text manipulation functions - TEXTBEFORE and TEXTAFTER

A common task in Excel is to extract a portion of text from a longer piece of text.

For example, here we want to extract only the invoice number from a list of invoice references.

The “traditional” approach

Before the release of the new text functions in Excel we might have approached this kind of problem with some combination of the following functions:

RIGHT – pick out a number of characters on the right-hand side
LEFT – pick out a number of characters on the left-hand side
MID – pick out a number of characters in the middle
LEN – return the length of the text (number of characters)

Let’s walk through the logic here. In this example we want to pick out the number, which is on the right-hand side, so we can expect to use the RIGHT function.

There is an added complication here in that we don’t know how many characters we need to pick. The invoice number could be anywhere between three and six digits long.

However, we do know that the text on the left-hand side “INV-“ is of a fixed length – it is four characters long - so we can exclude that by subtracting four from the overall length of the text.

Our formula then becomes:

=RIGHT(B4,LEN(B4)-4)

It doesn’t exactly trip off the tongue, and there is a bit of thinking involved in calculating the full length of the text and then excluding the unwanted characters.

Using the new TEXT functions

There is a new approach we can use, taking some functions that have recently been introduced into Excel:

TEXTBEFORE – pick out text before a delimiter
TEXTAFTER – pick out text after a delimiter

In this case we can simply pick out the text after our delimiter (being the dash symbol) and our formula becomes TEXTAFTER(B4,”-“).

This is significantly easier to understand and avoids having to use multiple functions and nesting one inside the other!

A more colourful example

Our client is an interior designer and has purchased tins of paint of various colours. Our input text now includes the paint colour before the invoice number (on the left-hand side) and the date of purchase (on the right-hand side).

As with the previous example we are aiming for the same end result – to pick out the invoice number – but this requires a more sophisticated approach as the invoice number is buried in the middle of the text.

Thankfully, the new Excel functions come to the rescue here. Firstly, we can “chop out” everything to the left of the delimiter (dash symbol) by using TEXTAFTER(B4,”-“).

Then once we have done that all we need to do is remove everything after the first space by using TEXTBEFORE(“D4”,” “).

And voila we have the desired result! We can easily combine these two steps into one by using the following formula:

=TEXTBEFORE(TEXTAFTER(B4,"-")," ")

Being a more complicated example, we can’t avoid nesting one function inside another. But for comparison (and this comes with a “do not try this at home” warning) this is still significantly easier than attempting to reach the same result with the “traditional” functions:

I hope it is perfectly clear at this point that the new functions are ideal for dealing with this kind of problem in Excel!

Alternative approaches

There are many ways to split text in Excel. For example, you could use PowerQuery, or the ‘Text To Columns’ feature, or even the ‘Auto Fill’ option. These are all good solutions in the right circumstances. However, if your spreadsheet requires a more dynamic solution, it is hard to find a better approach than Excel functions, as they are built directly into the worksheet and refresh automatically with any new data inputs.