0:00
hello and welcome to tutorial's point in
0:02
this video we will be covering three
0:04
functions text split text before and
0:08
text after and I'll show you exactly how
0:10
to use them now let's get
0:13
started here we have the sample data set
0:15
of full name first name last name
0:17
username email product code and city now
0:20
our first task is that we have full
0:22
names here and from these full names we
0:25
want to extract first name and last name
0:28
now one way is to manually write it down
0:30
but let's say you have thousands of
0:32
cells like this so it will take a lot of
0:34
time in this case we will use the text
0:37
split function so here in the first name
0:39
I will write that function I'll write
0:42
equals text split and then open
0:46
parenthesis and after this I will select
0:49
this full name cell and then a comma
0:52
after this I want to separate it on the
0:55
basis of a space as you can see there is
0:57
a space between first name and last name
1:00
so here in quotation marks I'll write a
1:03
space and then I'll close the quotation
1:05
mark and then the closing parentheses
1:08
when I hit enter as you can see we have
1:10
the first name and last name and now
1:13
I'll just drag the cursor down and we
1:16
have all the first names and last
1:19
names now next let's say we have this
1:22
email as you can see we have email of
1:25
different persons and based on this
1:27
email I want to write username names
1:30
here so basically let's say the first
1:32
email is james. Smith atgmailcom so for
1:36
this I want the username to be james.
1:39
Smith and similarly for others as well
1:42
so in this case we are going to use text
1:45
before a function so here in the
1:47
username I will write
1:50
equals text before and then open
1:54
parentheses and then I will select this
1:56
email so this sell and then comma and
2:00
text before as you can see next we want
2:03
to write the D limiter now in this case
2:05
the D limiter is going to be at the rate
2:07
so I'll write in quotation marks at the
2:10
rate and I'll just close the parenthesis
2:13
this means that we are looking for text
2:16
before this ad theate symbol all right
2:18
if I hit enter as you can see we have
2:21
the username james. Smith now we can
2:24
simply drag this down and we have all
2:26
the usernames or I can just go a step
2:29
back and instead in the formula instead
2:31
of selecting this one only I can select
2:34
all of these CES so here instead of
2:37
selecting just one cell I will just
2:40
select all of these cells and hit enter
2:43
and we have all the usern names in One
2:46
go now the last task here is to extract
2:51
City from this product code as you can
2:54
see in the product code at the end we
2:56
have name of the Cities right so we want
2:59
to extract act this name in this column
3:02
so in this case we will use the text
3:05
after function so here I'll write
3:08
equals text after and then open
3:12
parenthesis and then we will select the
3:14
text which in our case is this cell all
3:17
right and then a comma Now next is the D
3:21
limiter so we want to separate text on
3:24
the basis of this delimiter that is
3:27
going to be a hyphen so I'll just just
3:30
put it in the quotation marks and then a
3:32
comma but notice here in this text we
3:35
have two hyphens this is the first one
3:37
and this is the second one we want the
3:40
second hyphen so in so here I will write
3:42
in the instance number I'll write two
3:45
which means the second one so basically
3:48
this formula says I'm looking for text
3:51
after the second hyphen I will just
3:54
close the parenthesis and hit enter and
3:57
we have the city name now I can either
4:00
drag this down and we have we will have
4:02
all the names or simply just like we did
4:05
earlier instead of selecting the one
4:07
cell only I can just go back and select
4:10
all of these cells and then hit enter
4:13
and as you can see we have all the
4:18
go that's all for this video on the text
4:22
split text before and text after
4:24
functions you can also download this
4:26
practice file from the link in the
4:28
description below if you found this
4:30
video useful don't forget to hit the
4:32
like button share it with your friends
4:34
and subscribe for more Excel tips
