The RIGHT function in excel is used to extract a specific number of characters from the right side of a text string. The RIGHT function is similar to the LEFT function, however it extracts from the first character on the right-hand side of a text string, up to the number of characters specified by the user through the num_chars argument whilst the LEFT function extracts from the left-hand side of a text string. In this post, we would walk you through all there is to know about the RIGHT function and how you can use it in your financial models. Let’s begin with the syntax & argument.
Syntax & Argument
The syntax of the RIGHT Function is as follows:
=RIGHT(text, [num_chars])
As we can see from the above, there are only 2 arguments, one is required and the other is optional. Let’s see what they mean below:
text: This represents the cell containing text or hardcoded text string from which you want to extract characters.
[num_chars]: This is the optional argument, and it is used to specify the number of characters to the right side of the text string. Like we stated, it is an optional argument, and if it is not provided, the function extracts only one character from the right side of the text string.
Let’s move on to some of the usage notes of the RIGHT function.
Usage Notes
- The text argument must be a text or a reference to a cell containing a text string. If the text argument is not a text string, the RIGHT Function would return a #VALUE error.
- The num_chars argument must be a positive integer or refer to a cell containing a positive integer. If the num_chars provided is zero or a negative number, the RIGHT function will return an empty string (” “).
- If the num_chars is omitted, the RIGHT Function will return the first character to the right of the text.
- If the num_chars argument is greater than the length of the text argument, the RIGHT function will return the entire text argument.
- The RIGHT Function can be combined with other functions such as CONCATENATE, FIND, LEN for more complex text manipulation.
- If the text argument contains non-printable characters such as line breaks, the RIGHT Function may not return the expected result.
RIGHT Function Examples
Hardcoded Text
Let’s assume that our text string is the word “Golden” hardcoded in our RIGHT function. Our task is to extract the first 3 rightmost characters. Let us see how it looks below:
From Cell Reference
In this section, we would select our text string from a text. To do this, there is no need for double quotes, just select a cell like you would when using any function and specify the number of characters. Our text string for this section is “Management” and we want to extract the first four words rightmost of the text string.
RIGHT Function & CONCATENATE
Our next stop is combining the RIGHT Function with the concatenate function. In our example for this section, we have order details of some customers for a restaurant that sells only 3 products, and we want a summary column for these orders.
As you can see from the above image, our invoice details combine the food and actual invoice number, however we want to create a summary that contains the customer’s name, food type, food quantity and invoice number in a readable text. The formula to use is as follows:
Now, just copy and paste to other cells below:
Extract text to the right of Character
We can extract text to the left of a specific character by combining the LEFT function, LEN Function and FIND function. We have a simple example, let’s show you how to do it and the thinking behind it.
Our text above is Rice_krispies and our target is to extract all text to the right of the underscore delimeter (“_”). To do that, we use the formula below:
Explaining the formula
The FIND Function locates the position of the character we specify which in our case is the underscore delimiter (“_”). Next is the LEN Function which counts the total number of characters in our text string. These two functions are included in a calculation that derives the num_chars argument for the RIGHT Function. Excel takes the total characters (13) and subtracts the position of our underscore delimiter (5) from it, to derive the number of characters after the underscore delimiter. So we have the equation:
“13 – 5 = 8“
Finally, the RIGHT function takes 8 as the num_chars argument and returns 8 characters to the rightmost side of our text string.
