8 hours 33 minutes
so far you have seen, uh, how math functions can help you do quite
complex mathematical calculations.
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.
The reasons for that might be very various. For example, you need to import
text data from other programs and they may not be formatted in the way you want them.
Uh, people entering text made
make the stakes with case, for example, they can enter
to capital letters in the name extent off one they can, uh,
at extra spaces where they shouldn't be.
So all these things that when you type, for example, in inward, thes things usually get off the corrected. Here in excel,
you have to
pay attention to these things. Also, if you exporting data to some other programs, you might be required to reformat your daytime too specific
type of for mating of texts in order for that
program to accept it. For example, if you have ah, no name and surname ing in two separate cells
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.
So let's start with functions. First function is char
and it basically returns. A single character corresponding to ask echoed off their argument number
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
like in this example here
Ah, line break within the text.
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
ah added character 10 s key code 10
and this character represents the line break.
Now when you look a TTE this, you don't see much of it.
But, uh, if you form itself so that the text is wrapped
you get this forced
enter or within the middle off the text.
Why would you need that? For example, in some tables, you would like in order to save space because there are so many cones
You want the text to be broken in the middle in two lines instead of one line
so he doesn't overlap, or
there there should be a decent space between,
uh, explanations of calm. So in this case,
you would definitely need that.
Now. The next
function we're going to mention is clean
and clean removes none printable characters from the text string.
And why would you need that when you build your own
text? You probably won't put 10 in unprintable characters inside.
But if you import text from
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
character 11 which is rather crazy character, which shows like a small box here in the cell a one
and to remove it. We're just going to type clean
and a one,
and we get just ABC. So this non printable character, which is character 11 has just bean removed.
So next function has ah named that most people don't like to pronounce his concoct in eight,
and it basically joins more text strings into one.
example of this function.
Yeah, here it is.
It's difficult to type.
Is this one comma? This one? Come on, this one Come out. This one cover
and we get this. So
it's very simple. Uh,
why would you need this function? It
It is problematic because you can do the same thing.
Yours again sign and the simple formula
and like this
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
Expansion, we're going to talk is exact
ah kind of
inquiry function that checks whether two strings are exactly the same or not, and returns to a false.
And this is exactly the same means that it is case sensitive as well. So if you have
case like here,
so we say exact
off this comma, this
clothes plus center we get false.
And here we the way we have
exact match, including capital a. The beginning of the text get true.
So this function has used in checking things within
some queries and look up functions.
Ah, by itself it has no special value.
So next function,
if we're going to talk about his find
and find finds, position off one text ring in other texting
and it has ah additional option of start number on. Let me show you how it works.
So if we have text, which is
a B A b a b A b,
we want to find
And the text is
B A B sorry.
Within this text string
close it press center will get too. So it starts at the position too.
But if you say OK, let's look from the turd character.
We'll get four because on third position is a so be Abie starts at fourth place in this strength,
this is very useful. It works pretty much like the find function and combined with some other functions, you can extract
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.
and one more
function that we're going to talk about is left
and left is
the function that returns specifying number of characters from the beginning of the text
So we can say
this and with then say five
and we close it
and we get just Alfa.
Now imagine that we want to remove everything in this text
before the first space.
Well, days then See left. Sorry,
we have this text
and then we will have fine which we just talked about
And then what we're looking for is
within dis text here.
You don't need a starting them. So we look for the first appearance,
Ansari and we have to subtract one
because we don't want this space which we're going to find to be in the final
the result of this formula. So we want it
And then look at this way. Look att This result we copied and we paste just the values.
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
the part of the text from the middle, for example between two spaces
or sit in similar fashion
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand