This guide covers everything you need to know about the Google Sheets RIGHT function, including its definition, syntax, use cases, and how to use it.
What is the RIGHT Function? How Does It Work?
The RIGHT function in Google Sheets lets you grab a certain number of characters from the end of a text string. Think of it like snipping off a piece of text from the right side. It comes in handy when you’re tidying up your data or when you want to focus on specific parts of your text.
How does it work? Well, the function needs two things: the text you’re working with and the number of characters you want from the end. If you only give it the text and skip the number of characters, it’ll just grab the very last character for you.
An interesting thing about this function is that if you put 0 as the number of characters, it won’t give you anything back. It just returns an empty value.
Here’s a quick example: If you type RIGHT(“lorem ipsum”), Google Sheets will hand you back ‘m’ because it’s the very last character. But if you type RIGHT(“lorem ipsum”, 2), you’ll get ‘um’, which are the last two characters.
RIGHT Syntax
The syntax and arguments for the function are as follows:
RIGHT(string, [number_of_characters])
- string: This refers to the text you want to work with. It can be either the text directly or a cell reference that contains the text.
number_of_characters: This is optional. If you don’t include it, the function will automatically grab just one character from the end. If you do include it, you’re telling the function how many characters from the end of the string you want to see.
Here are some important notes about the syntax and arguments of the RIGHT function:
- The string argument is required, while the number_of_characters argument is optional. If you don’t specify a number_of_characters, the function will default to 1.
- The number_of_characters argument can be zero. In this case, the RIGHT function will return an empty string.
- The RIGHT function will return an error if the number_of_characters argument is negative.
- If the number_of_characters argument is greater than the length of the string, the function will return the entire string.
- The RIGHT function does not support wildcards like “?” and “*”.
- Both arguments can be dynamic. This means you can use cell references and other functions as arguments.
- The RIGHT function is not case-sensitive.
- The string can contain any characters, including numbers, symbols, and spaces.
- The RIGHT function ignores leading and trailing spaces in the string.
- The RIGHT function can handle strings up to 50,000 characters long. If the string is longer than this, the function will return a #VALUE! error.
Examples of How to Use the RIGHT Function
Here are some practical examples to illustrate the use of the RIGHT function in Google Sheets:
Example #1: Extracting Specific Characters from a String
For the text “GoogleSheetsFunction,” when extracting the last 8 characters using the RIGHT function like:
=RIGHT(“GoogleSheetsFunction”, 8)
The function will return “Function”.
Example #2: Extracting the Domain from an Email Address
If you have a list of email addresses and you want to extract the domain from each address, you can use the RIGHT function. For the email address “john.doe@gmail.com,” to extract the domain, you can apply:
=RIGHT(“john.doe@gmail.com”, LEN(“john.doe@gmail.com”) – FIND(“@”, “john.doe@gmail.com”))
The function will return “gmail.com”.
Example #3: Calculating the Last Two Digits of a Year
If you have a date in a cell and want to extract the last two digits of the year, you can use the RIGHT function. For a date like “15/03/2023,” to get the last two digits of the year:
=RIGHT(YEAR(“15/03/2023”), 2)
The function will return “23”.
Example #4: Extracting the File Extension from a Filename
If you have a filename in a cell (like “document.pdf”) and you want to extract the file extension, you can use the RIGHT function. For the filename “document.pdf”, to get its extension use this formula:
=RIGHT(“document.pdf”, LEN(“document.pdf”) – FIND(“.”, “document.pdf”))
The formula will return “pdf”.
Example #5: Extracting the Last Word from a Sentence
If you have a sentence in a cell (like “The quick brown fox” in cell A5) and want to extract the last word, you can use the RIGHT function. Here’s how:
=RIGHT(A5, LEN(A5) – FIND(” “, A5, LEN(A5) – LEN(SUBSTITUTE(A5, ” “, “”))))
This function will return “fox”, the last word of the sentence in cell A5.
Why Is RIGHT Not Working? Troubleshooting Common Errors
If you’re trying to use the RIGHT function in Google Sheets and it’s not working as expected, you are likely facing one of several common errors. These errors occur when the function is not used properly, and each has a specific cause and solution.
#VALUE! Error
Cause: The #VALUE! error is one of the most common ones you’ll encounter when using the RIGHT function. This error occurs when the function’s argument is non-textual or is not recognized as a valid text entry.
Solution: To address this error, ensure that the text argument in your function is a valid text entry. Ensure there are no numbers, special characters, or other non-textual elements unless they are supposed to be part of the text string.
#NUM! Error
Cause: The #NUM! error typically appears when the number of characters specified to be returned from the string is either less than zero or non-numeric.
Solution: To fix this error, you need to ensure that the number of characters you want to extract from the text string is a positive number and does not contain any non-numeric characters.
#REF! Error
Cause: The #REF! error is another common error when using the RIGHT function. This usually occurs when your formula references a cell that does not exist or when the cell reference is invalid.
Solution: To resolve this error, revise your formula to ensure it references a valid cell. Check your formula for any typos or incorrect cell references, and correct them if necessary.
#ERROR! Error
Cause: The #ERROR! error generally appears when the RIGHT function is written incorrectly, such as missing parentheses or other syntax errors.
Solution: To fix this error, carefully review your RIGHT function for any syntax errors. Ensure you have opened and closed all parentheses correctly and that all commas and other punctuation are in the right place.
#NAME? Error
Cause: The #NAME? error pops up when Google Sheets doesn’t recognize the text in the formula. This could be due to a misspelled function name, like writing “RIGH” or “RIGTH” instead of “RIGHT”.
Solution: To resolve this error, you will have to check your formula to ensure you have spelled the function name correctly. The correct spelling is “RIGHT”. If you have any spelling mistakes, correct them, and your formula should work.
For more details on the RIGHT function, check out the official documentation at the Google Docs Editors Help Center.
