Excel - Text before & after Function Tutorial

In this video, you will learn about Microsoft Search.
Microsoft Search allows you to quickly access features that can be hard to find, such as email signatures and comments.
It also helps you find new useful tools and work faster by directly accessing the features you want to use.
For example, you can check and remove duplicates from your documents in just a few clicks.
Additionally, you can find and open documents installed in your OneDrive or SharePoint and share them with your colleagues directly from the search bar.
This tutorial provides a concise and fluent overview of Microsoft Search, ensuring the best SEO visibility for your Microsoft 365 e-learning context.

602 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.

RELATED COURSES This tutorial is part of the following courses

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

Your trainer for this course
Cours E-learning

164 vues

ALL COURSES