RIGHT works just like LEFT did in Excel Video 155 with similar parameters. First, you tell Excel where the text string is you want to extract characters from. Second, you tell Excel how many characters to extract. The trick with RIGHT is knowing how many characters to extract. You’ll see in Excel Video 156 that we’re still using the comma between the last and first names to determine where the last name ends and the first name begins. Knowing where the first name begins isn’t enough. Since we’re starting from the right end when we extract characters, we have to know how long the first name (and middle initial in this example) is to know how many characters to extract.
Here’s the trick. By using LEN, we can tell how long the total name is. The FIND function tells us how many characters are in the last name plus the comma. Therefore, the difference between the total name length (LEN) and the number of characters in the last name plus the comma (FIND) has to be the number of characters in the first name (and middle initial in this example), plus the space between the comma and the first name. If we tell Excel to extract the total length of the name, less the last name and comma, less one more for the space between the comma and the first name, we’ll have the first name (and middle initial). Play with this idea once or twice to see how it works.
I know it’s more confusing to have the middle initial in this example. I left the middle initial in the example for two reasons. First, the real world has lots of middle initials. Second, there’s a function to get just the first name and skip the space and middle initial. We’ll discuss MID next time.
