Hello, everyone, in this lecture, we will cover some of the most important textual functions.
Textual functions are used to perform operations on the text type of data.
For example, if we need to remove any particular word from a group of words or if we need to extract
first five or last five characters of any word.
For such operations, we need to use textual functions.
So let's get started with the first textual function.
Here you can see we have student one reinterred in the first column, and you can notice that there
are unwanted extra spaces in front of it.
Similarly, the second and third and one values have extra spaces either in between or in the end of
the string.
To correct this, either we can edit each one of these text strings or we can use a simple brain function.
Brain function removes extra spaces from the text string.
It will remove spaces from the beginning or at the end or even between two words also.
Let's look at our example in this cell.
There are extra spaces in the front of strings to rent one.
Using Trem, we will remove these extra spaces.
Using trim function is very similar to using other mathematical formulas.
First, to start the function we have to type is equal to, then we will maintain the function name
that is Prem.
And after that, we will mention the text string we want to correct.
And presenter.
You can see extra spaces are removed.
Now, let's move on to the next example, you can see extra spaces between student and the numeric one.
Again, we will use brain function to remove these extra spaces, we will follow the same steps.
And now you can see that there is only one space between the word student and numeric character one.
Brain function has removed the extra spaces which were present between these two words.
Now, for the third row, you can see that there are extra spaces at the end of the street.
Again, we use train function, we can simply drive, we function from about.
And now the extra space is at the end of the.
However, these cells contain a formula instead of the required string.
To get strength, we know what we have to do.
We use the copy and paste as values technique.
These cells now have actual string values with no extra spaces.
Now, let's move on to the next function.
Suppose I have two strings and two sails, let's say first sail contains first name and second sail
contains last name.
Now, how do we combine these two strings to create a fully.
For such situations, we can use concatenate function to combine the two strings.
Let's first try to combine English, math and science.
As always, we will start our formula with equal to simple, then with eight concatenated.
You can see that in this cell.
We have all the three strings joined together.
First comes Matt, then without any space comes the next train which is signed, and then comes the
third string, which is English.
To further understand the use of this function, let us try to create a table where instead of just
marks, we will write that Student One has scored so many marks in this subject to do this.
We go to the sale and they concatenate.
Select student Vonzell Guerma.
Right, this clip between double quotation marks.
Space has called space.
Comma, select marks from table one.
Guma.
Now, again, into the script in quotation marks, space in.
And Max.
When I press enter, you can see that the complete sentences created student one has scored 95 in that
you can see in the form lobar concatenate function is taking student one from the cell has scored is
an input by us.
Marks is coming from the upper table, and then there's the string, which is an input from our site.
Not to extend this for others to rent, you can simply double click on the bottom right corner and the
entire column will be filled.
You can see for student fame also, the entire string is correct, student five has scored forty five
Inmet.
Concatenation can also be done using ampersand symbol ampersand is the symbol that we see on the numeric
key seven on a keyboard, and it looks like this.
This is also the symbol that we use while writing text.
So now in the English column, we will apply the ampersand symbol to get this same design.
To do this, select student Vonzell.
I am pushing.
Under the string, within double quotation marks, as called.
Ampersand again.
Moncks.
Within the regulation marks in English.
You can see that ampersand performs the same operation as concatenated function in concatenation.
It's like a normal function when you first mentioned the function and then give all the strings as parameters
in person.
It is like using plus symbol.
You write the string and then you write another string and then imposing another string and so on.
Next up is substitute function.
It is used to substitute a particular strain with another strain and the selected text.
For example, to rectify the spelling of student.
To rectify the spelling of student retape is equal to substitute.
Selected text in which you want to change the strings, comma, in the part of the string that you want
to replace, always remember to put quotation marks while maintaining strings.
We want to change be to the so the third parameter is what you wanted to be changed.
You can see the text Stuben is now student.
Same thing can be done in sentences also for second row of the table, we can update this score in the
sentence from 38 to 95.
So STUDENT One scored 95 monks in St..
In the third row, we can change the text science to English.
The important thing to note here is the word science is gaining steam.
If you make the best of science small, it will not be able to recognize the same.
You can alternate in the formula column, we have written the formulas, but these formulas are not
returning the values in this cell.
You can still see the formula written.
To do this, we have used a single space in front of the vehicle to assemble it is because of this space
axle is not calculating the actual value of this formula and showing this whole thing as a test in this
sense.
If I remove this space and enter, you can see that the formula will be calculated and the value of
formula will be shown.
So in this way, if you want to show the formula to someone without calculating the value, you can
just add a single space in front of the equal to symbol.
Next is the upper and lower function.
These functions simply convert the entire input string to upper or lower case.
For this.
We tape is equal to upper.
Lake District.
You can see that student one string is now completely uppercase.
In the next cell, we will use lower.
And you notice that the.
As of the student, which was earlier, uppercase is now Lorqess.
To completely fill the table with the boat, the sails go to the bottom right corner and double click
and the entire table is filled.
After this, we look at the land function.
Lenth function returns the land of the import string, that is, it returns the count of characters,
including spaces in the string.
So, for example.
In the fossil, we have student one written.
Student contains seven characters, then there is a space and then there is a numeric one.
Total, it contains nine characters.
So we will now use the land function to see how many characters does this actually help?
Len.
And then we select Destry.
You can see that this length function returns the number of characters, including the space in between.
We can extend it for the other string's.
You can see that the next ringland 11.
You can manually check if XLE is giving us the correct answers.
The last set of functions we are going to discuss are left, right and mid.
Left function will help us select a particular count of characters in the string from the left.
In this table, we have QR code values, which consist of three different parts.
One is the heir of manufacturing, which are the first for collectors of the QR code.
The last two characters are the group of that product.
And in between, there are five characters which represent the productivity of that particular product.
To segregate these three parts from the given QR code, we first use the left function.
To apply left function, we start taping left.
Select string, comma, type four.
This will return the first four characters of this selected string.
You can see that the first full strength, that is two zero one eight is written by the left function.
Right.
Does the same thing, it just starts the counting from the end of the string.
For example, here, we want to get right to characters of this string.
So we debride,
we select destroying.
And then type two.
You can see the right two characters have been returned.
Made is like left only difference is that you can change the starting point for the count.
For example, if you want the fifth to nine characters from this QR code you tape made.
Select restring.
Give the starting point, which is the fifth character.
And then give the number of characters.
Here we want five characters.
You can see that the middle five characters are written by this made function.
We can select these three cells and double click on the bottom right corner to complete this, they
will.
Like I told you in the previous lecture, all the textual functions can also be found in the formulas
tab of the Excel menu.
I would recommend that you go and explore these other textual functions also.
That's all for this lecture.
See you in the next one.