Excel - Text before & after Function Tutorial

4:57
96 views

00:00:00

New functions are now available

00:00:01

in Excel if you have the

00:00:04

2208 version to treat strings.

00:00:06

To verify your current version of Excel,

00:00:08

you need to go to the option file.

00:00:12

Account. And check if you are on

00:00:17

a Microsoft 365 app for business

00:00:19

and check on the about option

00:00:21

the version of your Excel.

00:00:26

Check if the version is at least 2208.

00:00:28

In my case I got the 2210.

00:00:35

The function we will talk about in this

00:00:38

tutorial is the text after and before.

00:00:40

Two functions that can help you

00:00:42

extract a part of any text before,

00:00:45

between and after a delimiter.

00:00:47

To access these function

00:00:50

you can go to the assistant here or

00:00:52

you can directly write the function.

00:00:55

In the cell.

00:00:57

For my first example I will go to the

00:01:00

insert function or assistant option.

00:01:03

Then go to the category

00:01:05

called text to find it.

00:01:07

To use the text before,

00:01:09

I want to extract the name of the

00:01:12

seller before the space separator.

00:01:14

Knowing the name also has the

00:01:15

last name and the country.

00:01:19

So I will click on the cell that contains

00:01:22

the full string and specify the delimiter.

00:01:25

In my case I will put a

00:01:29

space between quotes and that's all.

00:01:30

I have extracted the name of the cell.

00:01:32

For the second example I will enter the

00:01:37

formula manually for text before.

00:01:40

And I will extract the

00:01:42

name before the 2nd space.

00:01:44

I have to click on the cell

00:01:46

and specify the delimiter.

00:01:51

I will also specify that I

00:01:53

want to extract all the text

00:01:55

before the 2nd delimiter.

00:01:57

This is called instance NUM

00:01:59

or second delimiter space.

00:02:03

But I can't extract the name and last

00:02:06

name directly to extract the country.

00:02:08

So I will use the text after formula.

00:02:11

I will give you a tip because here it

00:02:13

becomes more difficult as I have to

00:02:15

count how many spaces there are between

00:02:17

the country and the rest of the street.

00:02:20

I have to specify the delimiter

00:02:22

and the space. To avoid all this

00:02:24

I will put a negative number.

00:02:27

This option allows you to tell

00:02:29

itself that you want the exact

00:02:31

space before the last word,

00:02:32

and what Excel does is to count the

00:02:35

space from the end to the start

00:02:37

depending on the chosen formula.

00:02:39

So now you can extract the

00:02:41

country to extract the forename.

00:02:44

I will use 2 formulas in one cell.

00:02:50

I will begin with text after.

00:02:54

So I specify the delimiter

00:02:56

space and I also tell its cell

00:02:58

that I want to extract it all

00:03:00

after the first delimiter

00:03:02

I extract the forename,

00:03:05

the serial number and the country.

00:03:08

Now I will use

00:03:12

the text before formula to extract it all.

00:03:21

And now I've extracted the full

00:03:24

name for the serial number.

00:03:26

It is nearly

00:03:27

the same thing

00:03:30

but with another delimiter.

00:03:32

I will extract all the numbers after the

00:03:35

delimiter of my cell with a text after.

00:03:38

So in this case the name ID to stop

00:03:42

the extraction and keep only the

00:03:44

serial number . So I use the text before

00:03:47

as usual and specify to stop the

00:03:50

extraction at the space separator.

00:03:52

So if I extract the serial

00:03:54

number of the seller.

00:03:56

These two formulas have optional parameters.

00:03:58

Let's see what they are.

00:04:06

First of all, the match mode.

00:04:09

By default the delimiter

00:04:11

match is case sensitive.

00:04:13

If you don't want your formula

00:04:14

to be case sensitive, you have to

00:04:16

disactivate the case sensitive option.

00:04:18

By typing zero or one in the match mode.

00:04:22

If you want more information,

00:04:24

I invite you to click on the help option

00:04:26

area to get a deeper explanation.

00:04:36

The match argument allows you to

00:04:38

specify if the end of the text will

00:04:40

be considered or not as a delimiter.

00:04:45

The if not found argument allows

00:04:49

you to specify which word will be

00:04:52

shown in your cell if the formula.

00:04:54

Or if there is an error produced.

Excel - Text before & after Function Tutorial

RELATED COURSES This tutorial is part of the following courses

Microsoft Excel Video Training – Intermediate