Maruf
Google Sheets is one of the leading spreadsheet applications with a variety of very useful functions and features. In Google Sheets, the RIGHT function allows us to extract the rightmost characters from a string. You can also combine this function with some other functions to return the desired results. In this article, we will explain the syntax of the function and demonstrate the use of the function using some suitable examples. Hopefully, this will help you to learn about this function in detail.
A Sample of Practice Spreadsheet
You can download the practice spreadsheet from the download button below.
What Is RIGHT Function in Google Sheets?
The RIGHT function returns a substring from the end of a specified string.
Syntax
The Syntax of the RIGHT function is shown below.
Arguments
| ARGUMENT | REQUIREMENT | Function |
|---|---|---|
| string | Required | The reference string i.e. texts, numbers, or other values; a portion from the right or end of this will be returned. |
| number_of_characters | Optional | This is the specific number of characters that the function will return from the end of the string. The default value is 1. Entering 0 will return an empty string. |
Output
The formula RIGHT(“ABCDEFGH”, 3) will return FGH as the first 3 characters from the right of ABCDEFGH is FGH.
6 Suitable Examples of Using RIGHT Function in Google Sheets
We can use the RIGHT function to extract necessary data from a string. We can combine it with other functions and use it in our spreadsheet.
Suppose, you have a dataset containing some user ID Codes. The IDs contain the first names of the users and a unique code separated by an underscore (“_”) character. We will extract the codes from the strings using the RIGHT function.
Follow the examples to learn how to do that.
1. Use RIGHT Function to Get a Substring
Here, if you observe the dataset, every user id has a unique code containing six-digit numbers. We will show how to use the RIGHT function to separate them in another column. Follow the steps to learn how to do that.
📌 Steps:
- First, select cell B5 and insert the following formula in the formula bar.
=RIGHT(B5,6)- Then, the formula will return the six-digit code in C5.
- After that drag down the Fill handle tool to get the rest of the codes.
- Finally, you will get the result shown in the image below.
2. Return Substring from Texts
In this example, we will use ARRAYFORMULA with the RIGHT function. This will return substrings from multiple strings at the same time. The ARRAYFORMULA function converts a regular formula into an arrayformula. This helps the formula to return results of a specific range at once. So you don’t need to use the fill handle tool like in the above example.
- Enter the following formula in cell C5 and you will see all the outputs at once without dragging down the formula.
=ARRAYFORMULA(RIGHT(B5:B10,6))
Read More: How to Use ARRAYFORMULA in Google Sheets (6 Examples)
Similar Readings
- How to Format Date with Formula in Google Sheets (3 Easy Ways)
- Use the DATEVALUE Function in Google Sheets (An Easy Guide)
- How to Compare Text in Google Sheets (3 Easy Ways)
- How to Highlight Duplicates for Multiple Columns in Google Sheets
3. Extract Characters from Strings
We can also use the RIGHT, LEN, and FIND functions together to extract a substring from a string. The LEN function returns the length of a string and the FIND function finds the position of a specific character within a string. We will use these three functions to extract a substring from the string.
Follow the steps below to see how to apply that.
📌 Steps:
- First, select cell C5 in the given dataset and insert the formula as shown below.
=RIGHT(B5,LEN(B5)-FIND("_",B5))- Then drag down the fill handle icon to copy the formula into the cells below.
- After that, you will get the same results as earlier.
➣ FIND(“_”,B5): The FIND function returns the position of the “_” character within the string in cell C5 i.e. 6.
➣ LEN(B5): The LEN function returns the length of the string in cell B5 i.e. 12.
➣ LEN(B5)-FIND(“_”,B5): So the subtraction becomes 12 – 6 = 6.
➣ RIGHT(B5,LEN(B5)-FIND(“_”,B5)): So the final formula becomes RIGHT(B5,6) and returns 6 characters from the right i.e. 648230.
Read More: How to Remove Characters from a String in Google Sheets (6 Easy Examples)
4. Search and Return Characters from Texts
Unlike the FIND function, the SEARCH function is not case-sensitive and returns the positions for both uppercase and lowercase letters. You can apply this function with the RIGHT function to find characters and substrings from texts if need to search for both uppercase and lowercase letters.
The steps are mentioned below.
📌 Steps:
- First, enter the mentioned formula in cell C5.
=RIGHT(B5,LEN(B5)-SEARCH("_",B5))- Then drag the fill handle icon down to apply the formula to other cells.
- It will extract the numbers like the following image.
➣ SEARCH(“_”,B5): Here the SEARCH function returns 6 as it is the position of the “_” character in cell B5.
➣ LEN(B5): The LEN function returns 12 as it is the length of the string in cell B5.
➣ RIGHT(B5,LEN(B5)-SEARCH(“_”,B5)): Finally, the RIGHT function will return the characters from the end of the string.
Read More: How to Use SEARCH Function in Google Sheets (5 Examples)
5. Extract Values from Strings
Though the outputs obtained in the earlier methods looks like numbers, they are actually in text format. Because the RIGHT function always returns text outputs. So, you can not use the outputs as numbers. But you can utilize the VALUE function to convert those number-looking texts to actual numbers.
- Apply the following formula in cell C5 and drag the fill handle icon below to do that.
=VALUE(RIGHT(B5,6))
- Here the VALUE function converts number-looking text strings to actual numbers.
Read More: How to Convert Text to Date in Google Sheets (3 Easy Ways)
6. Remove Certain Characters from Texts
In this example, we will combine the RIGHT and LEN functions to remove a certain number of characters from the beginning of strings.
Consider the following dataset containing website addresses with full URLs. Assume you need to remove the “https://” parts from the URLs.
- Then apply the following formula in cell C5 and drag the fill handle icon below.
=RIGHT(B5,LEN(B5)-8)
➣ LEN(B5)-8: Here the LEN(B5) returns 25 as it is the length of the string in cell B5. We need to subtract 8 from that as “https://” contains 8 characters. So LEN(B5)-8 returns 25 – 8 = 17.
➣ RIGHT(B5,LEN(B5)-8): Now the final formula becomes RIGHT(B5,17). So the RIGHT function returns the remaining 17 characters from the ending of the Full URLs in cell C5 i.e. https://www.wikipedia.org. The final output is www.wikipedia.org.
Read More: How to Remove First Character in Google Sheets
What to Do When RIGHT Function Is Not Working in Google Sheets?
There are some examples given above to show various uses of the RIGHT function. But there are times when you may face the RIGHT function not working properly. Mostly it happens because of extra spaces in the string.
- Consider the following example that uses the RIGHT function. The RIGHT(B5,6) formula in C5 should return 6 characters from the end. But as we can see, it is only returning 4 digits.
- Now double-click on cell B5 and you will see there are 2 extra spaces in the string. As the formula is counting them as characters, it seems the output contains 4 digits only. Delete the spaces and you will see the correct output.
Read More: How to Use TRIM Function in Google Sheets (4 Easy Examples)
Things to Remember
- The RIGHT function cannot work with dates. Because the RIGHT function work with text strings and dates are integer numbers.
- The RIGHT function always returns the output as a text string. So if you want to use the output as a number, you have to use the VALUE function like the above example.
- The RIGHT function shows a #VALUE! error if the number_of_characters argument is negative or less than zero.
Conclusion
We have tried to show you the uses of the RIGHT function in Google Sheets. Hopefully, the examples above will be enough for you to understand the applications of the function. Please use the comment section below for further queries or suggestions. You may also visit our OfficeWheel blog to explore more about Google Sheets.
Related Articles
- How to Use SUBSTITUTE Function in Google Sheets (7 Examples)
- Use FIND Function in Google Sheets (5 Useful Examples)
- How to Get Rid of Dollar Sign in Google Sheets (3 Effective Ways)
- Format Date with Formula in Google Sheets (3 Easy Ways)
- How to Use LEFT Function in Google Sheets (5 Suitable Examples)
- Google Sheets: Convert Text to Number (6 Easy Ways)
- How to Fill Down an Entire Column in Google Sheets (4 Easy Ways)
