Right() Function Examples

Welcome to Excel Intuition’s Right() Function Examples Video. Suzanne here. If you haven’t already done so, I recommend you download the companion workbook so you can examine it, learn from it, and try things on your own. I’m going to now jump to the Separating Text Example 1 worksheet in this video, (in this companion workbook), and take a look at the definition of the Right() Function. It has one required argument, the text string that you want to extract from the right of, and the number of characters is an optional argument. If you don’t include that argument, then the default value will be to extract one character from that text string, from the right. If you want to extract more than one, then usually you’ll need to know what the

presumed format of the entire text string is, so that you will know how many characters you want to extract, depending on what you’re trying to end up with. In this case, we want to extract the part of the name that includes the First Name and any Middle Name or Initial. So we have to calculate the number of characters by knowing the length of the entire text string and the location of the “,” (comma), which is used to define where the Last Name ends. So, in the Right() Function, in this circumstance, the first argument is the entire Complete Name text string in column A, and the number of characters is the total length of the text string, minus the location of the “,” (comma), minus 1. So, you can check it out for this first instance to see if that looks appropriate, because you can see everything. And the total length is 12, minus the location of the comma which is 7, gives you a result of 5, and subtracting 1 from that gives you 4, which is the total number of characters in the first name.

Now I’m going to jump to Example 2 using the Right() Function. In this case, we’re wanting to be able to extract the Middle Name or Initial from the right of the name. We’re not going to include the First Name this time. So in order to do that, we have to be able to count how many characters are in the Middle Name or the Middle Initial. We have to know if there is a second ” ” (space), because we only have a Middle Name or Middle Initial if there IS a second ” ” (space). So in this case we need an IF() Function, because we’re going to have two different possible outcomes: either an empty value (“”) for the Middle Name or Initial, or the correct value.

Using our IF() Function, we’ll use an Iserror() Function for the Logical Test it’s testing, to see if there’s an error in the search for a second ” ” (space). If it’s TRUE that there’s an error, then our Middle Name or Middle Initial will just be an empty value (“”). If it’s FALSE that there’s an error, we’re going to use the Right() Function, extracting from the complete name in column A, but this time our length is going to be the

Complete Name length in column B, minus the location of the second ” ” (space), which will be in column E, if there is a second ” ” (space). And in this case, we don’t have to subtract one from it, because it will be the correct value with just those two numbers to calculate with. And we can check it out in this third entry. We can see that the Complete Name length is 21, minus the second space location of 15, would give us a value of 6. And we can see that the Middle Name is indeed 6 characters long. Thank you very much, and come back for more text videos to see how the Mid() Function works if you haven’t already done so.