Hello everybody. Assuming that you have gone through our videos, our sessions on left and right formula, you now proceed towards mid formula made formula very powerful if you intend to extract data based on certain preconditions, which you want to configure. First, let's take help of an example. I have a couple of pan or a pan in India, every individual every initial assessee, probably a taxpayer who needs to get a pan registered permanent account number. So it's typically in a form where there are five characters in text followed by four in numeric form and the last one being a single character alphabetical character, but the unique part about this is the fourth character signifies the assessee status, and that I have given in the next sheet. So there is a table which says if the fourth character of the pan number is C, it means it's been registered under company.

If it's a fourth characterising p, it means a person so on so forth. So if so forth characters, let's say P, it says association of person, typically a trust. So now, how do I know that to these pan number, what associate status May I associate with? First I need to extract the fourth character by C equal to left, I choose the pan number, comma, and I say four. If I do that, notice it takes all four characters and extract them in the cell. I don't want that.

I want only one letter and that too from the fourth position. So here, I've using the mid formula mid chooses the cell after having done so it then tells me from which position do you want to start, I want to start from the fourth position. And from the fourth position, how many number of characters do you want to take out I want to take out one number of characters into There you go, one character as a copy pasted down, I get the full set of results. And not only that, just for a little bit of experimentation, had I written two. Can you guess what is that I'm going to get in the answer. From the fourth position, you're going to extract two characters, which means double P, and that's exactly what I'm going to get.

So my initial requirement was just one character, and I'll stick to it. And that's what I get. Now, if you apply a very quick v lookup, you will be able to find out what is the assessee status. So we look up look for letter P, comma, table array, which means I need to go to the other sheet where the table has already been prepared. As I choose it, I ensure that the range is fixed by pressing f4 once it has been fixed, I put a comma and then column index number two because the answers were in the second column of the table and finally false or zero. close the bracket.

So it looks for the letter P and finds out it's a person. What if individuals company, company person and false so there you go. This was a quick tutorial on mid formula and in Indian context, how do you extract the RCC status based on the pan number? In fact, generally I also apply another test logic which ensures that the length of characters are correct as it's supposed to be. So as I copy pasted down, notice one of the pan number has been misspelled because there are 11 characters which is not correct. It was just an add on.

The main point is with respect to mid formula in context of left and right