1
00:00:00,660 --> 00:00:07,650
Hello, everyone, in this lecture, we will cover some of the most important textual functions.

2
00:00:09,360 --> 00:00:14,630
Textual functions are used to perform operations on the text type of data.

3
00:00:16,020 --> 00:00:23,910
For example, if we need to remove any particular word from a group of words or if we need to extract

4
00:00:24,030 --> 00:00:27,390
first five or last five characters of any word.

5
00:00:28,660 --> 00:00:32,320
For such operations, we need to use textual functions.

6
00:00:34,570 --> 00:00:38,020
So let's get started with the first textual function.

7
00:00:39,730 --> 00:00:47,800
Here you can see we have student one reinterred in the first column, and you can notice that there

8
00:00:47,800 --> 00:00:51,000
are unwanted extra spaces in front of it.

9
00:00:54,280 --> 00:01:02,680
Similarly, the second and third and one values have extra spaces either in between or in the end of

10
00:01:02,680 --> 00:01:03,880
the string.

11
00:01:07,730 --> 00:01:16,250
To correct this, either we can edit each one of these text strings or we can use a simple brain function.

12
00:01:18,770 --> 00:01:22,790
Brain function removes extra spaces from the text string.

13
00:01:24,220 --> 00:01:31,360
It will remove spaces from the beginning or at the end or even between two words also.

14
00:01:32,580 --> 00:01:36,910
Let's look at our example in this cell.

15
00:01:37,320 --> 00:01:40,080
There are extra spaces in the front of strings to rent one.

16
00:01:41,200 --> 00:01:43,900
Using Trem, we will remove these extra spaces.

17
00:01:45,170 --> 00:01:49,490
Using trim function is very similar to using other mathematical formulas.

18
00:01:50,770 --> 00:01:57,250
First, to start the function we have to type is equal to, then we will maintain the function name

19
00:01:57,460 --> 00:01:58,360
that is Prem.

20
00:02:00,590 --> 00:02:05,560
And after that, we will mention the text string we want to correct.

21
00:02:08,590 --> 00:02:09,490
And presenter.

22
00:02:11,320 --> 00:02:13,870
You can see extra spaces are removed.

23
00:02:16,610 --> 00:02:22,730
Now, let's move on to the next example, you can see extra spaces between student and the numeric one.

24
00:02:23,930 --> 00:02:29,120
Again, we will use brain function to remove these extra spaces, we will follow the same steps.

25
00:02:37,520 --> 00:02:44,360
And now you can see that there is only one space between the word student and numeric character one.

26
00:02:45,980 --> 00:02:51,050
Brain function has removed the extra spaces which were present between these two words.

27
00:02:52,990 --> 00:02:58,410
Now, for the third row, you can see that there are extra spaces at the end of the street.

28
00:02:59,560 --> 00:03:04,330
Again, we use train function, we can simply drive, we function from about.

29
00:03:07,340 --> 00:03:10,190
And now the extra space is at the end of the.

30
00:03:11,830 --> 00:03:17,710
However, these cells contain a formula instead of the required string.

31
00:03:20,370 --> 00:03:23,340
To get strength, we know what we have to do.

32
00:03:23,790 --> 00:03:27,510
We use the copy and paste as values technique.

33
00:03:33,040 --> 00:03:38,140
These cells now have actual string values with no extra spaces.

34
00:03:40,750 --> 00:03:43,150
Now, let's move on to the next function.

35
00:03:46,820 --> 00:03:54,530
Suppose I have two strings and two sails, let's say first sail contains first name and second sail

36
00:03:54,530 --> 00:03:56,120
contains last name.

37
00:03:57,580 --> 00:04:01,750
Now, how do we combine these two strings to create a fully.

38
00:04:03,640 --> 00:04:08,560
For such situations, we can use concatenate function to combine the two strings.

39
00:04:10,880 --> 00:04:15,470
Let's first try to combine English, math and science.

40
00:04:16,570 --> 00:04:25,180
As always, we will start our formula with equal to simple, then with eight concatenated.

41
00:04:34,280 --> 00:04:36,240
You can see that in this cell.

42
00:04:36,650 --> 00:04:40,470
We have all the three strings joined together.

43
00:04:40,940 --> 00:04:48,440
First comes Matt, then without any space comes the next train which is signed, and then comes the

44
00:04:48,440 --> 00:04:50,360
third string, which is English.

45
00:04:53,730 --> 00:05:00,780
To further understand the use of this function, let us try to create a table where instead of just

46
00:05:00,780 --> 00:05:11,070
marks, we will write that Student One has scored so many marks in this subject to do this.

47
00:05:12,200 --> 00:05:16,190
We go to the sale and they concatenate.

48
00:05:22,130 --> 00:05:25,220
Select student Vonzell Guerma.

49
00:05:27,150 --> 00:05:29,730
Right, this clip between double quotation marks.

50
00:05:33,090 --> 00:05:35,790
Space has called space.

51
00:05:41,480 --> 00:05:45,830
Comma, select marks from table one.

52
00:05:48,660 --> 00:05:49,200
Guma.

53
00:05:50,470 --> 00:05:55,150
Now, again, into the script in quotation marks, space in.

54
00:05:59,680 --> 00:06:00,580
And Max.

55
00:06:06,280 --> 00:06:15,490
When I press enter, you can see that the complete sentences created student one has scored 95 in that

56
00:06:16,000 --> 00:06:25,210
you can see in the form lobar concatenate function is taking student one from the cell has scored is

57
00:06:25,210 --> 00:06:26,620
an input by us.

58
00:06:27,830 --> 00:06:35,060
Marks is coming from the upper table, and then there's the string, which is an input from our site.

59
00:06:36,680 --> 00:06:42,770
Not to extend this for others to rent, you can simply double click on the bottom right corner and the

60
00:06:42,770 --> 00:06:44,020
entire column will be filled.

61
00:06:47,430 --> 00:06:54,930
You can see for student fame also, the entire string is correct, student five has scored forty five

62
00:06:54,990 --> 00:06:55,620
Inmet.

63
00:06:59,020 --> 00:07:07,480
Concatenation can also be done using ampersand symbol ampersand is the symbol that we see on the numeric

64
00:07:07,870 --> 00:07:12,580
key seven on a keyboard, and it looks like this.

65
00:07:13,110 --> 00:07:17,560
This is also the symbol that we use while writing text.

66
00:07:19,170 --> 00:07:25,860
So now in the English column, we will apply the ampersand symbol to get this same design.

67
00:07:27,230 --> 00:07:30,680
To do this, select student Vonzell.

68
00:07:35,590 --> 00:07:37,060
I am pushing.

69
00:07:40,720 --> 00:07:45,610
Under the string, within double quotation marks, as called.

70
00:07:53,120 --> 00:07:54,110
Ampersand again.

71
00:07:56,250 --> 00:07:56,880
Moncks.

72
00:07:59,670 --> 00:08:01,860
Within the regulation marks in English.

73
00:08:18,510 --> 00:08:25,750
You can see that ampersand performs the same operation as concatenated function in concatenation.

74
00:08:26,460 --> 00:08:33,330
It's like a normal function when you first mentioned the function and then give all the strings as parameters

75
00:08:34,200 --> 00:08:35,070
in person.

76
00:08:35,160 --> 00:08:36,930
It is like using plus symbol.

77
00:08:37,320 --> 00:08:44,190
You write the string and then you write another string and then imposing another string and so on.

78
00:08:47,440 --> 00:08:49,720
Next up is substitute function.

79
00:08:51,240 --> 00:08:56,820
It is used to substitute a particular strain with another strain and the selected text.

80
00:08:59,180 --> 00:09:02,300
For example, to rectify the spelling of student.

81
00:09:03,660 --> 00:09:09,390
To rectify the spelling of student retape is equal to substitute.

82
00:09:16,150 --> 00:09:25,600
Selected text in which you want to change the strings, comma, in the part of the string that you want

83
00:09:25,600 --> 00:09:31,180
to replace, always remember to put quotation marks while maintaining strings.

84
00:09:35,150 --> 00:09:41,680
We want to change be to the so the third parameter is what you wanted to be changed.

85
00:09:50,660 --> 00:09:54,530
You can see the text Stuben is now student.

86
00:09:57,950 --> 00:10:04,940
Same thing can be done in sentences also for second row of the table, we can update this score in the

87
00:10:04,950 --> 00:10:07,990
sentence from 38 to 95.

88
00:10:29,340 --> 00:10:34,260
So STUDENT One scored 95 monks in St..

89
00:10:37,050 --> 00:10:41,470
In the third row, we can change the text science to English.

90
00:10:43,170 --> 00:10:47,270
The important thing to note here is the word science is gaining steam.

91
00:10:48,190 --> 00:10:53,550
If you make the best of science small, it will not be able to recognize the same.

92
00:11:07,370 --> 00:11:14,450
You can alternate in the formula column, we have written the formulas, but these formulas are not

93
00:11:14,450 --> 00:11:17,390
returning the values in this cell.

94
00:11:17,420 --> 00:11:19,590
You can still see the formula written.

95
00:11:20,970 --> 00:11:29,960
To do this, we have used a single space in front of the vehicle to assemble it is because of this space

96
00:11:30,180 --> 00:11:38,470
axle is not calculating the actual value of this formula and showing this whole thing as a test in this

97
00:11:38,470 --> 00:11:38,850
sense.

98
00:11:39,030 --> 00:11:46,650
If I remove this space and enter, you can see that the formula will be calculated and the value of

99
00:11:46,650 --> 00:11:48,300
formula will be shown.

100
00:11:51,340 --> 00:11:57,040
So in this way, if you want to show the formula to someone without calculating the value, you can

101
00:11:57,040 --> 00:12:01,000
just add a single space in front of the equal to symbol.

102
00:12:03,680 --> 00:12:05,750
Next is the upper and lower function.

103
00:12:07,480 --> 00:12:13,450
These functions simply convert the entire input string to upper or lower case.

104
00:12:15,200 --> 00:12:15,830
For this.

105
00:12:17,530 --> 00:12:19,570
We tape is equal to upper.

106
00:12:23,260 --> 00:12:23,880
Lake District.

107
00:12:28,370 --> 00:12:32,510
You can see that student one string is now completely uppercase.

108
00:12:33,600 --> 00:12:35,640
In the next cell, we will use lower.

109
00:12:42,320 --> 00:12:43,790
And you notice that the.

110
00:12:44,880 --> 00:12:49,170
As of the student, which was earlier, uppercase is now Lorqess.

111
00:12:51,750 --> 00:12:58,170
To completely fill the table with the boat, the sails go to the bottom right corner and double click

112
00:12:58,530 --> 00:13:00,180
and the entire table is filled.

113
00:13:02,950 --> 00:13:05,440
After this, we look at the land function.

114
00:13:07,800 --> 00:13:14,250
Lenth function returns the land of the import string, that is, it returns the count of characters,

115
00:13:14,490 --> 00:13:16,860
including spaces in the string.

116
00:13:18,740 --> 00:13:19,760
So, for example.

117
00:13:20,740 --> 00:13:23,640
In the fossil, we have student one written.

118
00:13:24,900 --> 00:13:30,750
Student contains seven characters, then there is a space and then there is a numeric one.

119
00:13:31,920 --> 00:13:35,190
Total, it contains nine characters.

120
00:13:36,980 --> 00:13:44,360
So we will now use the land function to see how many characters does this actually help?

121
00:13:47,890 --> 00:13:48,310
Len.

122
00:13:50,100 --> 00:13:51,540
And then we select Destry.

123
00:13:54,590 --> 00:14:02,690
You can see that this length function returns the number of characters, including the space in between.

124
00:14:05,330 --> 00:14:07,250
We can extend it for the other string's.

125
00:14:09,520 --> 00:14:12,940
You can see that the next ringland 11.

126
00:14:14,470 --> 00:14:17,760
You can manually check if XLE is giving us the correct answers.

127
00:14:19,330 --> 00:14:25,240
The last set of functions we are going to discuss are left, right and mid.

128
00:14:27,370 --> 00:14:33,970
Left function will help us select a particular count of characters in the string from the left.

129
00:14:36,110 --> 00:14:41,360
In this table, we have QR code values, which consist of three different parts.

130
00:14:42,950 --> 00:14:47,930
One is the heir of manufacturing, which are the first for collectors of the QR code.

131
00:14:49,570 --> 00:14:52,840
The last two characters are the group of that product.

132
00:14:53,850 --> 00:15:00,240
And in between, there are five characters which represent the productivity of that particular product.

133
00:15:02,340 --> 00:15:08,340
To segregate these three parts from the given QR code, we first use the left function.

134
00:15:10,030 --> 00:15:13,320
To apply left function, we start taping left.

135
00:15:17,540 --> 00:15:21,200
Select string, comma, type four.

136
00:15:23,950 --> 00:15:28,060
This will return the first four characters of this selected string.

137
00:15:30,860 --> 00:15:37,160
You can see that the first full strength, that is two zero one eight is written by the left function.

138
00:15:39,420 --> 00:15:40,020
Right.

139
00:15:40,050 --> 00:15:44,550
Does the same thing, it just starts the counting from the end of the string.

140
00:15:46,250 --> 00:15:51,470
For example, here, we want to get right to characters of this string.

141
00:15:52,760 --> 00:15:53,720
So we debride,

142
00:15:57,050 --> 00:15:58,060
we select destroying.

143
00:15:59,570 --> 00:16:00,950
And then type two.

144
00:16:04,320 --> 00:16:07,230
You can see the right two characters have been returned.

145
00:16:10,650 --> 00:16:17,240
Made is like left only difference is that you can change the starting point for the count.

146
00:16:18,470 --> 00:16:25,250
For example, if you want the fifth to nine characters from this QR code you tape made.

147
00:16:29,230 --> 00:16:30,100
Select restring.

148
00:16:32,050 --> 00:16:35,050
Give the starting point, which is the fifth character.

149
00:16:36,840 --> 00:16:38,850
And then give the number of characters.

150
00:16:41,430 --> 00:16:43,050
Here we want five characters.

151
00:16:49,550 --> 00:16:55,310
You can see that the middle five characters are written by this made function.

152
00:16:56,770 --> 00:17:02,530
We can select these three cells and double click on the bottom right corner to complete this, they

153
00:17:02,530 --> 00:17:02,740
will.

154
00:17:05,720 --> 00:17:12,110
Like I told you in the previous lecture, all the textual functions can also be found in the formulas

155
00:17:12,110 --> 00:17:13,520
tab of the Excel menu.

156
00:17:16,620 --> 00:17:21,090
I would recommend that you go and explore these other textual functions also.

157
00:17:22,830 --> 00:17:24,030
That's all for this lecture.

158
00:17:24,180 --> 00:17:25,230
See you in the next one.