### Video Transcription

00:01

we're continuing

00:03

ext functions with the function England,

00:08

and it is a simple function that returns the land of next drink.

00:13

So just to show you height works,

00:17

just tape course

00:21

and then

00:24

this one

00:25

and you get nine because this one has nine characters. And if you expand the function, too,

00:30

next cell

00:32

you see that the club has

00:35

for

00:36

Weathers, and this is why the limit is four.

00:40

Now. This function by itself doesn't have much of a use, but you'll very soon see how

00:46

it is very useful, combined with other functions.

00:50

So

00:51

next function

00:54

is lower, and it's also very simple function that converts all letters in a tank string to lower case. Now, there are very few situations where you would just need to do that.

01:07

But in some situations

01:10

Ah, you would, uh, probably,

01:12

er, use that in

01:15

projection with the other functions.

01:19

For example, if you want to make sure that

01:23

in ah sentence only the first word has the first capital letter and the remainder off

01:30

the text is our case.

01:34

Uh, so

01:37

this is it,

01:38

and

01:41

this is it.

01:44

And now we can to run very useful function, which is called mid

01:49

and it returns the strength, the string with the given length and from given position off original strength. So if you have string that says 12345 and then we use mid function and start with number three and then we number four you will get a string with just three and four

02:10

to see how it functions is that we have here already pretty different formula. And I'm going to go through with it because it's a rather complex formula and you have to understand what it does.

02:24

So

02:25

first argument is the text string which were working on which is a one,

02:32

and then we have ah, to find

02:37

and we want to separate

02:38

the

02:39

second word in this three word strength.

02:45

So we're looking at

02:47

for his position and the first position. And now there is a great use for the find

02:53

we say find. And then we're looking for the space

02:58

in a one

03:00

here

03:00

and when you get that number, we add one more, because here first

03:07

and space. But space is in position six

03:09

and we want to start with next position in the string, which is second, which s in second and it's number seven. So we add one

03:20

and then we want to find the length and D Lent

03:23

is again find.

03:27

And then we again. We're looking for a space in a one.

03:31

But

03:32

we want to start looking at the position which is the position off first space plus two and then subtract from it the position off the first base

03:46

and then minus one. So this looks probably very complicated at this point,

03:53

but this is how it works. So we're finding the position off the second space

04:00

on. And we do that by looking at the

04:04

space in this

04:06

1st 2nd 3rd text string,

04:10

but not from the beginning, but from the position, which is one position Maur

04:15

are after the position of first base.

04:19

And then

04:21

we subtract from that

04:25

the position off the first base. And then again we subtract one more

04:30

and we get second. And to make sure that this is without any spaces,

04:35

we're going to copy this as well you.

04:40

And when you look at here, we don't have any spaces before, and no spaces after word second.

04:47

So this is how mid works

04:50

it is

04:54

recommended for you to look at this formula studied and tried to find Why is it

05:00

done like that?

05:03

So the next function is number of value

05:08

and when it clears tex to number in any format that was written into why is it important? So, for example,

05:17

we have ah, countries

05:20

in which

05:23

decimal separator is not

05:26

point. It's Kama

05:28

and 1000 separator is point, not comma.

05:32

So

05:33

we will

05:34

live here

05:36

number well, you

05:40

and then we're going to

05:43

look a TTE the text which is going to be

05:47

converted into number

05:50

and then we're going to type in the decimal separator

05:55

which in this case is comma.

05:58

And then we're going to type in the thousands separator which in this case, it's worry is

06:04

ducts

06:06

and we're going to close it

06:09

and we're going to get the number in the usual way

06:15

oath

06:16

typing it. Actually, this is just the number and we had formulated this cell tohave to decimal places and to use 1000 separators which in this case, is comma.

06:30

Next function is proper

06:33

and it basically converts names to proper case. So

06:36

the result is always going to be the first letter is upper case in the others are lower case. It doesn't matter. How many words do we have in

06:47

the cell or the text? We're applying the proper function too.

06:53

So

06:54

just to see how it

06:55

works. So

06:57

presumably somebody has entered,

06:59

uh, the name

07:01

typing errors.

07:02

You're just going toe

07:08

corrected with proper function.

07:11

It is important to remember that we cannot use this correct sentences because it will put the uppercase letter at the beginning of every word.

07:20

And this is not what you want to do with

07:25

sentences.

07:27

Now we're going to look at the function replace

07:31

and replace

07:33

is basically replaces all text with new one within strength

07:39

except the old text.

07:43

You have to enter

07:45

the starting position in which you're doing replacement

07:48

number of characters you're replacing and new text, which you're using to replace it with.

07:56

So this is not like replace. You have in the, for example, Microsoft word

08:03

where it will find

08:05

the text and replace it. Now you have to find it yourself.

08:09

And to explain how to do it

08:11

is going to

08:13

be a little bit more complicated. But

08:18

again.

08:20

So the old text

08:22

is this,

08:24

and we want to replace thes two peas

08:28

with one p.

08:30

So we're going to use the find function

08:33

and we're looking for double B

08:39

within

08:43

this text here.

08:46

And

08:50

this is our starting position.

08:54

And the length is, too, because we're replacing two peas

09:00

and the new text is going to be one

09:05

and we're to close it

09:07

and we get the proper spelling. Of course. How did we get to it?

09:11

It's a different story, but let's say that we know that this kind of typing error has been made and we want to do it.

09:20

So next function is right

09:26

and we're going to

09:28

so right function is basically one that returns specified number of characters from the end of text drink. So if we have a B, c, D E f and we say right

09:39

and we put that text string and then for a number of characters, we put,

09:45

too, and it was a B. C. D. We will get the Andy,

09:50

So

09:50

let's get back to do the example, and we have the same example like we had in the mid.

09:58

But now we want to separate the last word in the

10:03

string,

10:03

so we're going to type right

10:07

and then it gets complicated from here. So this is the next year

10:13

looking into,

10:16

and then we're going to

10:20

find

10:22

the number of characters. So the number of Carter is length off

10:26

this year,

10:28

minus

10:31

the position off second space

10:35

and position off second space. We'll find, we find.

10:39

And then we're going to look a TTE space

10:45

in

10:46

a one.

10:48

And then it's going to be

10:52

find

10:54

again

10:56

space

11:03

and the

11:05

again in a

11:09

one. So we're looking at the first position

11:13

and that position

11:16

to the position we're going toe. Add

11:18

two

11:20

in close one, too,

11:24

and we get third. This is what we were looking for.

11:28

So again, like we admit, it's a complicated function.

11:31

But if you put your mind to it, if you're right, it on the paper before you type it in or you type it in and do some try whenever you will understand how to do it. Of course,

11:43

if you're you can always use this example and copy, paste it. It's clear

11:50

way to do it simply

11:52

if you're looking for the same thing

### Up Next

### Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

### Instructed By

Milan Cetic

Instructor