### Video Transcription

00:00

Okay, This is the last part of ex functions. Listen,

00:05

And the next function I'm going to talk about this search.

00:10

It's pretty much the same function as find Onley. This one is not case sensitive, so it will find a certain string within another string and it will give a cz Ah return the position of that string with that string.

00:29

Also, you can define from which character in the search string are you starting from the search. And, uh

00:40

So just to see

00:43

how it looks,

00:45

we can do it like this. So we're going to look for to Capitol Peas in this text

00:51

here that has lower case bees

00:55

and we're going to start from position one.

00:58

And

01:00

here it is. We get

01:02

as a return the number two because PP starts on the second position. Now, if we change here 23 we will get nine

01:11

and you see it is still finding people. Though we're looking for capital to kept a piece and we have lower case

01:19

keys

01:21

apiece. So that's Ah, that's the search. Pretty much the same as find.

01:26

Next function is substitute and it replaces one text ring with the other tech string

01:34

eso. It works like

01:37

replace in ah, inward, for example.

01:42

And

01:47

so to make an example So this is the text we're looking in, which we're doing the replacement.

01:53

So, for example, the old text is

01:59

BP.

02:00

The new one is just one B

02:04

Sorry,

02:05

small,

02:07

lower case p

02:08

and then we are not going to change it

02:13

in the first instance. But in second instance

02:16

and we get this so it's very simple.

02:21

Also, if we would change this to one,

02:24

we would get this.

02:27

So it's Ah, it's very

02:30

six successfully designed

02:34

function that can really help you in in many situations.

02:38

Um,

02:39

the next function

02:43

we're talking about is T,

02:46

and it's ah,

02:47

very strange run.

02:50

So

02:52

it's basically checks if value is the text and returns the text if it is or returns double close meaning empty text. If it's not

03:00

so. The usefulness of this function is not quite clear at this moment, but you can very nicely use it in the Mac rose, um,

03:13

to eliminate anything that is not text. So, um,

03:19

it's just going to be t

03:21

out of this.

03:23

And we get this. And now if we expend this,

03:28

we get

03:29

or logical value older. It looks like testes is illogical.

03:34

Think and it returns nothing.

03:37

And here it's also text. Now, if we change this

03:43

into

03:45

fixed,

03:49

nothing happens because it's still recognizes. This is a number, so it doesn't matter if we have form. It'd the cell as ah

03:55

ah text. It sees the number and doesn't return it.

04:01

So the next function is text function

04:05

and text function. Basically,

04:08

convergence number two texting specified format

04:13

and you can enter Form it in many ways. But

04:16

let's see how it can be done.

04:19

So for example,

04:21

we can die Peter

04:25

next.

04:27

And then here is what we want to change to text

04:31

and then we can say

04:35

Come on

04:41

and

04:47

yeah,

04:48

we should have typed thes

04:54

related.

04:58

And

05:00

here it is.

05:00

It gives us no decimal places because there are no decimal places.

05:04

But if we change this, we get

05:09

these things also

05:12

here we can say Ah,

05:15

for example,

05:16

we just removed these and remove these

05:21

and put your percent

05:25

and we get

05:27

percentage former thing automatically. Because this is 22 it is automatically said to be

05:33

2200% so if we want actually 22%.

05:39

It would take 0.22 we get 22%. So the possibilities are limitless. You just have to follow the standard X self former thing guide, and you get what you want.

05:53

Next function is trim

05:57

and trim removes multiple spaces from text drink. It is a simple is that nothing else

06:02

and

06:04

to see how it works.

06:09

We have one text that has deliberately multiple

06:13

spaces in it,

06:15

and it will correct it so you don't have

06:18

and the

06:19

unwanted spaces in this text. So it works just like that. Nothing else.

06:27

And we have two more functions. The next money's upper

06:31

and upper simply converts. Old letter to upper case.

06:35

So we have a name

06:38

and we say upper

06:42

and

06:43

changes like this.

06:45

We get upper case. It is doing the opposite off lower function.

06:50

Nothing

06:51

especially about it. But it can be used very successfully in

06:57

ah,

06:58

some other functions to get what you need and value converts text to number. It's simple. Is that so? This is nothing special

07:08

with value, so you can have this text and see

07:12

well, you

07:14

off this

07:16

and you get the number in the former thing that exists in that cell. So if we want to get the correct form fitting, we would then form itself. We would click a number,

07:30

and then we would probably say something like this and we would get to the former thing we want. You see that? It it doesn't care if you have 1000 separators or

07:43

ah, whatever kind. It will get you just the number you want. And this concludes the lesson Number two in this marshal.

### 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