RIGHT Formula in Excel (Table of Contents)
Excel RIGHT Formula
Excel RIGHT function is a built-in function that is categorized under text/string function which can be used to extract the specified number of substrings from a string starting from the rightmost letter or from the end of your string.
Syntax
Argument in RIGHT Formula
- text – Required argument which specifies the text/string or the cell containing the string you wanted to extract the substring from.
- num_chars – Is an optional argument that specifies the number of characters to be extracted from the given string from the left-hand side or start of the string.
- If num_chars is omitted, by default value 1 will be considered which means the last character of your string will be published as a result.
- If num_chars is greater than the length of your string (or total number of characters in your string), RIGHT formula will publish the entire string as an output.
- If num_chars is negative, RIGHT formula will give #Value! error.
How to Use RIGHT Formula in Excel?
RIGHT Formula Excel is very simple and easy. Let’s understand how to use the RIGHT Formula in Excel with some examples.
Example #1 – Extract Substring from a String
Suppose you have a string as shown in the cell given below.
In Cell B1, start typing the formula for RIGHT formula as shown below.
The first argument for the RIGHT function is text value from which we need to extract the substring. Mention A2 as the first argument for the RIGHT Formula.
Add numeric value 6 as a text specifier which specifies the number of substrings you wanted to extract from the main string through the right-hand side.
Press Enter Key to see the output. You’ll see an output as below.
This is how the excel Right function can be used. It just simply extracts the substring from the right of your string based on the number of characters argument you specify.
Example #2 – Extract Substring after a Specific Character
Suppose we have data like below which has State Code along with Postal Code details.
What if we need to extract the postal code details from this combined text? We can achieve this result with the combination of excel RIGHT, LENTH and SEARCH function together. Let’s see this step by step.
Start typing excel RIGHT Formula under Cell B2.
You have to give the first argument as the string out of which you want to extract the substring. In this case, you can give A2 as a first argument because it contains text value you want to extract the substring from.
Use the LEN function to get the length of the cell containing the string.
As the function will extract the data from right, it should know the exact length of the text so that it can search the specific delimiter and return the substring expected.
Now, use the SEARCH function to find out the specific delimiter in the cell containing text and subtract it from the length of text in A2.
Well, don’t get confused though it seems somewhat confusing through naked eyes. Let us try to evaluate the logic behind this.
- SEARCH function gives the position at which the delimiter value occurs in our cell (3rd position). That position value is getting subtracted from the total length of cell A2 (length of cell A2 is 9 which includes 7 character letters, one comma, and one space). So, after the subtraction (9 – 3 = 6) we get 6 as a num_chars argument of RIGHT function. Thus, RIGHT function extracts 6 characters from right of the string.
Press Enter Key and drag the formula till the row B6 to see the output of this formula.
Here one obvious question comes in our mind that why can’t we directly provide the num_chars argument in this formula. The reason is that not every time the delimiter would be at exactly the same position. Sometimes, it might be a different position (for example in the case of full name). It would be difficult to manually provide the num_chars argument every time. Therefore we better automate the things using formula.
Example #3 – Store the Number Values Only
As we have discussed at the start of this article, the RIGHT formula is categorized under text formula in excel. If you extract any numerical value, it will be stored as a text. However, you might be in need to get those values as numbers only. Let’s see how we can do this.
Suppose we have data that contains the first name of the employee along with employee code which is a numeric value. We need to store this employee code as a number for further data calculations.
Start typing VALUE Formula in Cell B2 (We are doing this so that the value we are extracting can be stored as a number in excel).
Use excel RIGHT as an argument under VALUE function. It will take that formula as an argument because the RIGHT formula is for text manipulation and VALUE function can be used to convert a text value into a numerical value.
Give A2 as an argument to the RIGHT formula as it contains the string from which we need to extract the substring.
You can see that the employee ID is of length 4 from the right-hand side of the string for all the cells (It can also be manipulated using the LEN-SEARCH formula as we did in the previous example). Therefore, use 4 as a num_chars argument.
Press Enter key and Drag the Formula till Cell B6 to see the output.
You can see the output as in the screenshot above. The values from Cell B2 to B6 are aligned in column B. Therefore, you can say that those are being converted into numbers.
This is it from the article. Let’s wrap the things up with some points to be remembered.
Things to Remember About RIGHT Formula in Excel
- The default value for num_chars argument is 1. It extracts the first character string from the right of the string.
- The num_chars value should always be greater than or equal to 1. If it’s less than 1 (or negative in that case), it gives #VALUE! Error.
- If the num_chars value is greater than the length of the entire string, RIGHT formula gives an entire string as an output.
Recommended Articles
This is a guide to RIGHT Formula in Excel. Here we discuss How to use RIGHT Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
