Hi everyone, this video we talk about the formula right, left and mid. And to make it more interesting, I will attempt to use multiple other formulas and combined with the left right mid formula to give you a very fascinating combination to be able to apply that in practical life. Now on column A, I have a couple of names, which are ending with a four digit ID code. Now what is right formula do in its pure basic form, right formula? As I begin writing the formula, it tells you from which cell from which text form which cell, do you want to extract the characters from the right direction. So I put a comma because the next parameter says how many number of characters now I've been lucky so far in this data because there are four characters so far.
I'm going to put four and I'm going to close the parenthesis, enter. I close the bracket, there you go. So if you are given certain consistent number of digits or characters towards the end on the right hand side, you can use the right formula effectively to extract and pull the information. Now eventually, as we see the building blocks, how they work, we'll also get to scenarios and challenges where the data is all mixed up. At this moment, I'll have another succeeding challenge, which is, apart from the characters, I also want to extract the names the names ronning, Sonny, Tony Jane, Jackson, Harry and Kane using the formula left. Now I could have done so using text to column but this session, I'm purely dedicating myself to left formula, right formula and so on.
So let's see how the left will help me and what obstacles do I face. If I start writing equal to left? I choose the cell. And then I am told that how many number of characters do I need to extract, I put five I close the bracket. I'm happy. I'm getting Ronnie Getting sunny.
But when I go to the next couple of cells notice the names the names, for example, Jackson. It's not complete yet. Why? Because you had told the formula five characters only. So we need a mechanism, which tells me exactly how many number of characters needs to be extracted, and that cannot be a hard coded number. So we find out, let's see how, so I need to find out how many characters from the left.
To do that let me start with a formula called Len Len, closing the bracket, enter. Now notice this is the total length of the entire series of characters, including the digits and the spaces. Now fortunately, for me, the number of digits are only for a number. So if I reduce the entire length formula with minus minus four and minus one, although I could have done minus five, but this is just for our karmic purpose minus four is to ensure the full use are taken away from the land. One for the space. So what I'm getting is 5554475, fourths, and so on, which means Jackson is seven in character.
Now if you notice closely, how has this been calculated in total Jackson seven characters plus one space eight a plus 412. So from 12, you have taken away four and one which leaves you with how much seven. So now in our formula of left, what are we going to do? We are going to look for left, choose the data, comma, and then choose the number five. So this way, as I copy pasted, I get the full series of answers correctly. In fact, if I had a word let's say my name shop automatically picks it up.
Maybe even I gave a longer name think of a longer name. So Swami Narayan. It still works. It's a combination of left and lend does the trick. If you want to progress a little bit ahead, I will also suggest that you go to the made formula and that too in the next video