Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor