### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

### 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
IT Security Consultant
Instructor