Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
so far you have seen, uh, how math functions can help you do quite
00:08
complex mathematical calculations.
00:12
Now, a CZ Muchas excel is primarily to meant to be used for mathematical calculations. It can be also very powerful toe from manipulating text data.
00:24
The reasons for that might be very various. For example, you need to import
00:30
text data from other programs and they may not be formatted in the way you want them.
00:37
Uh, people entering text made
00:40
make the stakes with case, for example, they can enter
00:45
to capital letters in the name extent off one they can, uh,
00:50
at extra spaces where they shouldn't be.
00:54
So all these things that when you type, for example, in inward, thes things usually get off the corrected. Here in excel,
01:02
you have to
01:03
pay attention to these things. Also, if you exporting data to some other programs, you might be required to reformat your daytime too specific
01:15
type of for mating of texts in order for that
01:19
program to accept it. For example, if you have ah, no name and surname ing in two separate cells
01:29
and the pure exporting to program that requires name and surname to be in one text drink, you need to connect them together and similar stuff.
01:40
So let's start with functions. First function is char
01:44
and it basically returns. A single character corresponding to ask echoed off their argument number
01:49
and depression is why would you need that? Well, for example, you can need You may be in a position that you need toe add
02:00
like in this example here
02:04
Ah, line break within the text.
02:07
So when you see this text, it says sales 2019 Their space between sales in 2019 Here we don'ts have space but we have
02:19
ah added character 10 s key code 10
02:23
and this character represents the line break.
02:27
Now when you look a TTE this, you don't see much of it.
02:32
But, uh, if you form itself so that the text is wrapped
02:38
you get this forced
02:40
enter or within the middle off the text.
02:46
Why would you need that? For example, in some tables, you would like in order to save space because there are so many cones
02:54
You want the text to be broken in the middle in two lines instead of one line
03:00
so he doesn't overlap, or
03:02
there there should be a decent space between,
03:07
uh, explanations of calm. So in this case,
03:10
you would definitely need that.
03:15
Now. The next
03:19
function we're going to mention is clean
03:23
and clean removes none printable characters from the text string.
03:29
And why would you need that when you build your own
03:32
text? You probably won't put 10 in unprintable characters inside.
03:38
But if you import text from
03:42
some software like database, you might get some characters that shouldn't be there. So we don't have text important from some database. But we created the text that has
03:54
character 11 which is rather crazy character, which shows like a small box here in the cell a one
04:01
and to remove it. We're just going to type clean
04:08
and a one,
04:12
and we get just ABC. So this non printable character, which is character 11 has just bean removed.
04:20
So next function has ah named that most people don't like to pronounce his concoct in eight,
04:29
and it basically joins more text strings into one.
04:32
And, uh,
04:35
example of this function.
04:45
Yeah, here it is.
04:46
It's difficult to type.
04:48
Is this one comma? This one? Come on, this one Come out. This one cover
04:55
and clothes,
04:57
and we get this. So
04:59
it's very simple. Uh,
05:01
why would you need this function? It
05:04
It is problematic because you can do the same thing.
05:09
Yours again sign and the simple formula
05:14
like this,
05:16
like this
05:18
and like this
05:21
and you get absolutely the same result. However, sometimes it is good to have this formula, especially if you going to use my crow's. Then it's better to use this formula than
05:33
something else.
05:36
Expansion, we're going to talk is exact
05:41
and exact
05:44
is
05:46
ah kind of
05:48
inquiry function that checks whether two strings are exactly the same or not, and returns to a false.
05:58
And this is exactly the same means that it is case sensitive as well. So if you have
06:05
case like here,
06:09
so we say exact
06:12
off this comma, this
06:15
clothes plus center we get false.
06:18
And here we the way we have
06:23
exact match, including capital a. The beginning of the text get true.
06:28
So this function has used in checking things within
06:34
some queries and look up functions.
06:38
Ah, by itself it has no special value.
06:44
So next function,
06:46
if we're going to talk about his find
06:50
and find finds, position off one text ring in other texting
06:58
and it has ah additional option of start number on. Let me show you how it works.
07:05
So if we have text, which is
07:08
a B A b a b A b,
07:13
and then
07:15
we want to find
07:20
find
07:24
Sorry, find.
07:28
And the text is
07:30
B A B sorry.
07:34
Within this text string
07:42
here,
07:44
close it press center will get too. So it starts at the position too.
07:48
But if you say OK, let's look from the turd character.
07:55
We'll get four because on third position is a so be Abie starts at fourth place in this strength,
08:05
this is very useful. It works pretty much like the find function and combined with some other functions, you can extract
08:15
some texts or find some text within the text ring eso when used in Mac rose or in some complex look up functions, it can be very, very useful.
08:30
Ah,
08:31
and one more
08:35
function that we're going to talk about is left
08:39
and left is
08:43
the function that returns specifying number of characters from the beginning of the text
08:48
So we can say
08:50
left
08:52
from
08:56
this and with then say five
09:01
and we close it
09:03
and we get just Alfa.
09:05
Now imagine that we want to remove everything in this text
09:11
That is
09:11
before the first space.
09:15
Well, days then See left. Sorry,
09:20
left off
09:22
And then
09:24
we have this text
09:26
and then we will have fine which we just talked about
09:31
And then what we're looking for is
09:35
blank
09:37
within dis text here.
09:43
You don't need a starting them. So we look for the first appearance,
09:48
Ansari and we have to subtract one
09:52
because we don't want this space which we're going to find to be in the final
09:56
the result of this formula. So we want it
10:01
space too.
10:03
Be gone.
10:05
And then look at this way. Look att This result we copied and we paste just the values.
10:13
Look at here the reason of space at the end. So we have removed everything before the first base combined with some other functions. We can also remove
10:24
the part of the text from the middle, for example between two spaces
10:28
or sit in similar fashion

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