New Text Manipulation Functions

Locked lesson.

Upgrade

Quick reference

New Text Manipulation Functions (Microsoft 365, Excel 2021 only)

Use new Excel functions, TEXTBEFORE, TEXTAFTER, and TEXTSPLIT to manipulate text in a worksheet. 

When to use

We use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT to split up data across columns. 

Instructions

TEXTBEFORE, TEXTAFTER, and TEXTSPLIT are new functions that are available to users of Excel for Microsoft 365. 

These functions allow us to split up data across columns and cells much like existing text functions, RIGHT, LEFT, and MID. 

The difference with the new functions is that they are much simpler to use and have additional arguments that allow us to specify multiple delimiters and the delimiter instance. 

TEXTBEFORE and TEXTAFTER

In this example, we need to split up the name and extension number in column B across columns D, F, and H. 

Extract the First name

We can use TEXTBEFORE to extract the first name. Only the first two arguments of TEXTBEFORE are mandatory. 

text - the cell that contains the text. 

delimiter - the delimiter present in the string. 

Our delimiter is a space. If we don't specify an instance number, this formula will extract all text from cell B7 that is before the first space. 

We have now successfully extracted the first name. 

Extract the Full Name

This time, we need to extract the full name from cell B7. We can do this using TEXTBEFORE but we need to specify that we want to extract everything before the second space. 

We have now successfully extracted the full name. 

Extract the Ext. No

Next, we need to extract the extension number from cell B7. We can do this using a combination of TEXTBEFORE and TEXTAFTER. 

We can start by using TEXTAFTER to extract all text after the "x". 

This doesn't return exactly what we want. 

We can add TEXTBEFORE to extract everything before the bracket. 

Now we get the correct result. 

TEXTSPLIT

TEXTSPLIT allows us to specify one or more delimiters to split up text across columns. 

In this example, we have a list of subjects that we need to assign to each student. Notice that some of the subjects are separated with a comma and some subjects are separated with a semi-colon. 

TEXTSPLIT allows us to define multiple delimiters by placing them inside curly brackets {}. 

Excel knows to split the text string wherever it finds a comma or a semi-colon. 

In the next example, we need to use TEXTSPLIT to split up the full name. However, each name is not consistent. Some names have a title and others do not. We can define the space and the titles as delimiters inside curly brackets {}

Notice that wherever there was a title there is now a blank cell and everything has been shifted to the right. 

Let's modify the formula to ignore empty cells. 

We now have the correct result. 

 

Login to download
  • 00:04 A couple of lessons ago we were taking a look at how we can use text functions to
  • 00:08 split text up across different columns.
  • 00:11 And we saw how we can use left, and mid, and right.
  • 00:14 And what I said to you was that these can get pretty complex.
  • 00:18 So I thought this would be a good opportunity to showcase to you some of
  • 00:23 the brand new functions that have been released in Excel to make this process
  • 00:28 a lot simpler.
  • 00:29 Now, it is worth noting that these new text functions that I'm going to show
  • 00:32 you have ever been released in the last few months.
  • 00:35 So the only people that are going to have these are those who have a Microsoft 365
  • 00:40 subscription for Excel.
  • 00:42 So let's take a look at how this can be useful.
  • 00:45 Now the first two functions that we're going to look at for
  • 00:48 extracting text are TEXTBEFORE and TEXTAFTER.
  • 00:51 So let's see how they work.
  • 00:53 Now we're going to start off really basic.
  • 00:55 Again, we have our names and our extension numbers.
  • 00:59 And maybe I need to extract the first name from this list.
  • 01:03 Well, I can use TEXTBEFORE to do this.
  • 01:07 Now we've TEXTBEFORE we're basically extracting everything before
  • 01:11 a delimiter that we specify.
  • 01:13 So the first argument here is text.
  • 01:15 So where is the text?
  • 01:16 It's in cell B7.
  • 01:19 We then get to specify the delimiter that we want to extract everything before.
  • 01:24 So if I take a look at my data, I want to extract the first name, so
  • 01:29 I want to basically extract everything before the first space delimiter.
  • 01:35 And that's all we need here.
  • 01:36 If we close the bracket and hit enter, we can then fill down and
  • 01:41 we get those first names.
  • 01:43 Nice and straightforward.
  • 01:44 What about if we want to extract the full name?
  • 01:47 So this time instead of just Lucy, I want Lucy Oliver.
  • 01:50 Well, this is where TEXTBEFORE has advantages over some of the other text
  • 01:55 functions, because we can specify the instance of a delimiter.
  • 02:00 So what I can say here is look for
  • 02:02 the second space and extract everything before.
  • 02:06 So, let's go for TEXTBEFORE, again, our text is in cell B7.
  • 02:13 We are looking for the space delimiter, but the next argument is instance number.
  • 02:19 So I can say I want you to find the second space in the string and
  • 02:23 extract everything before.
  • 02:26 Close the bracket, hit enter, and like magic we've done that so
  • 02:30 much easier than some of those other functions.
  • 02:34 Now what about if I want to do something slightly more complex.
  • 02:37 Maybe I want to extract the extension number and
  • 02:40 I don't want to include the brackets or the x, I just want the number.
  • 02:44 Well, how would we go about this?
  • 02:46 Well, what I'm going to do here is I'm going to combine
  • 02:49 TEXTBEFORE with TEXTAFTER.
  • 02:51 We're going to go for TEXTBEFORE, our text is in cell B7, and
  • 02:55 what I'm going to say is I want to extract everything before that closing bracket.
  • 03:01 So my delimiter is going to be the closing bracket.
  • 03:06 Now, if I was just to add the formula here and hit enter,
  • 03:09 I'm going to get everything before the closing bracket.
  • 03:12 So what I can then do is edit this formula and
  • 03:16 say also extract everything after the x.
  • 03:20 So let's go up to the formula bar, we're going to add TEXTAFTER.
  • 03:27 A text is generated by a TEXTBEFORE function,
  • 03:31 we're going to say look for the x.
  • 03:36 And we're basically going to extract everything after that.
  • 03:39 And check it out.
  • 03:41 When we pull this down, we get exactly what we're looking for
  • 03:45 by simply combining TEXTAFTER with TEXTBEFORE.
  • 03:48 Now believe me, this is so much easier than messing around
  • 03:51 with the right function to extract things that are on the right hand side of a cell,
  • 03:55 that can get really, really complicated.
  • 03:58 So if you have these new functions, then your life has suddenly got a lot easier.
  • 04:03 Now, another new text manipulation function that we have is the TEXTSPLIT
  • 04:07 function.
  • 04:08 Now this works in a slightly different way to TEXTBEFORE and TEXTAFTER.
  • 04:12 So let's take a look at a couple of examples.
  • 04:14 Now with TEXTSPLIT, we can basically split up text across columns,
  • 04:19 but we can specify multiple delimiters.
  • 04:22 So, this is great if you have data that doesn't have a consistent delimiter.
  • 04:27 It doesn't always have a comma that's separating everything.
  • 04:29 Maybe it has a comma for some, and a space for another, and a semicolon for another.
  • 04:33 This is where TEXTSPLIT comes into its own.
  • 04:36 So what I basically want to do here is I want to take these subjects and
  • 04:40 I want to assign them to the correct people.
  • 04:43 So English needs to go in cell C8 for Claire,
  • 04:46 history needs to go in cell D8 for Ben, so on and so forth.
  • 04:50 Now one thing to note about this data here is that we do not have a consistent
  • 04:54 delimiter.
  • 04:55 So for some of these, we have a comma space separating the subjects.
  • 04:59 But here we have a semicolon space separating.
  • 05:04 So we have multiple delimiters.
  • 05:06 Well, this isn't a problem for the TEXTSPLIT function.
  • 05:11 All we need to do is define our text, so
  • 05:14 it's in cell B8, and then we can choose our delimiter.
  • 05:18 Now if I was just doing this for the first one, I would say,
  • 05:22 okay my delimiter is comma space, and I get exactly the result I'm looking for.
  • 05:27 But if I was to copy this down, it doesn't work for
  • 05:30 the next one, because we have a semicolon separating our words.
  • 05:34 So this is where we can use multiple delimiters.
  • 05:37 So let's double click to edit our formula.
  • 05:39 And if we want to use multiple delimiters, we need to wrap them in curly brackets.
  • 05:44 So I'm going to open a curly bracket.
  • 05:46 We still want add comma space delimiter, but we want to add another one in.
  • 05:50 So we're going to add in the semicolon space delimiter.
  • 05:55 And then we can close off the curly brackets, hit enter, and
  • 05:59 then we should find that when we copy this down, everything works perfectly.
  • 06:04 Now let's take a look at a second example of using TEXTSPLIT where we're going to
  • 06:08 use the ignore empties argument.
  • 06:10 Now what I have here are some full names, but
  • 06:13 you'll notice that some of these names have a title.
  • 06:15 So some people have Mr, Ms, and Miss, and others do not.
  • 06:21 Now, all I want to extract from here is the first name and the last name,
  • 06:25 I don't care about the title.
  • 06:27 So if I was just to say TEXTSPLIT, and maybe I'm looking for
  • 06:31 the first space, I'm going to get a problem.
  • 06:35 Because whilst it's splitting up those names, it's including the title.
  • 06:40 So we need to go about this in a different way.
  • 06:43 Now, to get this to split correctly,
  • 06:46 what we can do is we can define the Mr, the Miss, and the Ms as delimiters.
  • 06:52 So because we have multiple, we need to open a curly bracket.
  • 06:55 I'm going to define the space as my first delimiter, but
  • 06:59 then the second delimiter is going to be Mr.space.
  • 07:03 The third delimiter is going to be Ms, and the next delimiter is going to be Miss.
  • 07:09 So now I've defined all of those, I can close off my curly brackets,
  • 07:13 close off my TEXTSPLIT function, hit Enter, and we're almost there.
  • 07:18 It has got rid of the Mr, Ms, and Miss but it's just left a blank cell.
  • 07:24 So this is where the ignore empty argument comes into play.
  • 07:28 So if we jump up to our formula.
  • 07:30 I'm just going to click at the end here where we have our delimiters.
  • 07:34 Notice that one of the arguments is ignore empty.
  • 07:37 So I'm going to press comma and
  • 07:39 comma again to jump over the row delimiter argument, because we don't need that.
  • 07:43 And this is where I can choose if I want to ignore empty cells.
  • 07:46 So it's kind of similar to TEXTJOIN in that way.
  • 07:50 So I'm going to say, yes, TRUE.
  • 07:52 Let's ignore those empty cells.
  • 07:54 And then when we copy this down,
  • 07:56 we should find that everything looks exactly as we want it to look.

Lesson notes are only available for subscribers.

Gift this course