Excel has functions that allow you to grab just a piece of data from a text string and place it somewhere else. When the piece you want is at the end of that string, the Excel RIGHT function might be just the thing you need.
Purpose
The RIGHT function in Excel extracts a specific number of characters from a text string, starting from the rightmost character. This function relies on the relative position of characters in a text string.
Return value
The value returned by the RIGHT function is the character or characters in the original text string which meet the conditions stated in the formula.
Syntax
The syntax of the RIGHT function is:
RIGHT(text, [num_chars])
Arguments
The text argument is the text string from which you want to extract characters. This may be entered as a text string within double quotes inside the formula itself, or (more commonly) it could refer to a cell reference that contains the characters to be extracted.
The num_chars argument is optional. It tells Excel how many characters you want to be copied from the original text string, starting with the last (rightmost) character. If num_chars is omitted, it is assumed that you only want the very last character.
For example:
=RIGHT(“apples and oranges”,7) will return the word oranges.
=RIGHT(“apples and oranges”) will return the letter s.
Remarks
- If num_chars is zero, RIGHT will return an empty cell.
- If num_chars is not an integer, Excel will truncate the number and return the relevant characters.
- If num_chars is a negative number, RIGHT will return a #VALUE! Error.
Download your free practice file!
Use this free Excel file to practice along with the tutorial.
Using RIGHT to solve problems
Basic application
In its simplest application, RIGHT can be used to extract the last n characters from a cell, like in the example below.
=RIGHT(A2,2)
Using LEFT, MID, and RIGHT functions to split data of fixed length
RIGHT is sometimes used with the MID and LEFT functions to split values in one cell across several cells. As their names suggest, MID is used to extract characters from somewhere in the middle of a text string, and LEFT is used to grab leftmost values from the string.
We can use two or all of these functions when we want to break up a text string into separate pieces.
For example, the following dataset contains telephone numbers which we would like to split into three separate columns as follows:
- The area code, consisting of the first three numbers.
- The prefix, consisting of the next three numbers.
- The line number, consisting of the final four numbers.
Step 1
We would use the LEFT function to grab the first three numbers. The syntax of the LEFT function is:
=LEFT(text, [num_chars])
- Text is the text string to be split.
- Num_chars is the number of characters in text to return, starting with the leftmost character. If omitted, only the leftmost character is returned.
=LEFT(A2,3)
Step 2
Then we can use the MID function to get the next three numbers. The syntax of the MID function is:
=MID(text, start_num, num_chars)
- Text is the text string to be split.
- Start_num is the position number of the first character to be returned, counting from the leftmost character in text.
- Num_chars is the number of characters in text to return, starting with the leftmost character.
All three arguments in the MID function are required.
=MID(A2,4,3)
Step 3
Finally, we use the RIGHT function to extract the last four numbers.
=RIGHT(A2,4)
Using RIGHT, LEN, and FIND to extract strings of variable length
The problem is a little trickier when the values we want to extract are of variable lengths. If we want to get the last names from the following list, we wouldn’t be able to just use the RIGHT(text, [num_chars]) syntax because the last names aren’t consistent in length.
The syntax of the LEN function is simply:
LEN(text)
- Text is the cell reference or cell reference which contains the characters to be counted.
The syntax of the FIND function is:
FIND(find_text, within_text, [start_num])
- Find_text is the text you want to find.
- Within_text is the text string within which you want to search.
- Start_num (optional) is the position number of the character where you want to start searching. If start_num is omitted, FIND will start searching from the first character in the string.
We can use LEN and FIND to calculate the num_chars argument of the RIGHT function.
=RIGHT(A2,LEN(A2)-FIND(“ ”,A2))
We can copy the formula to the other rows for Excel to perform the same calculation and return the last names.
Note that the FIND function is case-sensitive. In this case, it doesn’t matter because we want to find a space character. But if you want to do an extraction using a non-case-sensitive character search, use the SEARCH function instead.
Excel RIGHT function troubleshooting
There are a few times when you might not get the result you expected when using the RIGHT function. Here are some common problems.
-
Working with dates
Understanding that RIGHT is a text function is very important. This means it will convert all values to Text format. So trying to pull the year only from cell A1 below will not return a value of 2021.
So when we ask Excel to use the RIGHT function to return the last four characters from that string, Excel returns 4485, the last four numbers which represent the string displayed to us as 16-Oct-2021.
The best (and simplest) solution for this problem is to use the YEAR function instead.
=YEAR(A1)
-
How to get a numeric output with RIGHT
Understanding Text versus Number formats is also useful in a situation like the one below.
We can extract the last five characters from a text string to get the zip codes from an address. However, notice that the numbers are aligned to the left of the cell. This indicates that these values are stored as text instead of numbers.
=VALUE(RIGHT(A2,5))
-
Fewer characters than expected
If you know how many characters make up your desired output, but your RIGHT formula only returns some of the characters, maybe there are extra spaces in your original text.
We can fix this by combining RIGHT with the TRIM function. TRIM is designed to remove all spaces from a text string except for one space between words.
=RIGHT(TRIM(A2),8)
Quick note on combining functions: If you’re just learning how to nest (combine) functions, here’s a golden tip. Whatever action you want Excel to perform first should be in the inner parentheses.
This is because since the result of those calculations will be needed for the other function(s), Excel wants to store that information in its memory for the subsequent actions.
That’s why for the situation above, we trimmed the full address first, then extracted the last eight characters. If we did it the other way around — TRIM(RIGHT(A2,8)) — the extra spacing would still be there.
RIGHT vs. RIGHTB
If you’re interested in learning about RIGHTB, you’ll want to know that the only difference between RIGHT and RIGHTB is that RIGHT returns the number of characters in a text string. RIGHTB returns the number of bytes used to represent the characters in a text string.
RIGHTB counts 2 bytes per character only when a double-byte character set (DBCS) language is set as the default language. The languages that support DBCS include Japanese, Chinese (Simplified), Chinese (Traditional), and Korean.
Otherwise, RIGHTB behaves the same as RIGHT, counting 1 byte per character. For this reason, we have only discussed RIGHT in this resource.
Sound off
Have you found any other uses for the RIGHT function? We’d love to hear about them. Leave a comment below!
You can also try our Excel Basic and Advanced course to continue boosting your Excel skills.
Ready to become a certified Excel ninja?
Start learning for free with GoSkills courses
Start free trial