Function to separate strings after characters in Excel What is that? That is the RIGHT function. Here's what you need to know about RIGHT function in Microsoft Excel.
If you often have to work with large amounts of data or numbers, Microsoft Excel is definitely a part of your daily life. Excel has long been famous for providing smart data processing solutions, especially fast and accurate calculations thanks to smart functions. Therefore, if you are learning Excel, you must learn details about how to use functions. RIGHT is one of them.
RIGHT is another popular function in MS Excel. It is often used to get the last character or in a string, based on a specific number of characters. Specifically, this function is designed to return a specified number of characters from the immediate right of a string.
The RIGHT function is currently used quite a lot in Excel spreadsheets. However, not everyone knows how to use it effectively. If you are one of them, please join Quantrimang.com to learn what you need to know about the RIGHT function in Excel below.
If the LEFT function separates the character string from the left, the RIGHT function does the opposite, separating the character string from the right in a character sequence that the user chooses. The RIGHT function then returns the result of the last one or more characters in a text string based on the number of characters displayed in the data cell. The article below will guide you how to use the RIGHT function in Excel.
Instructions for using the RIGHT function in Excel
The RIGHT function's syntax is: =RIGHT(text,[num_chars]). In there:
- text: is the text string you want to extract characters from.
- num_chars: the number of characters you want the RIGHT function to extract.
Note:
- If num_chars is omitted, its default is 1.
- Num_chars must always be greater than or equal to 0.
- If num_chars is greater than the length of the text string, the RIGHT function returns the entire text string.
Example 1: Use the RIGHT function to find characters
For example, with the data table below, extract the last 3 characters in each data cell. In the result box, enter the formula =RIGHT(B2,3) and press Enter.
As a result, we get the last 3 characters in the character sequence as shown below. Scroll down to the remaining cells to display other results.
In addition, we can also directly enter the sequence of characters as shown below but must use quotation marks.
Example 2: The RIGHT function finds numeric characters
These two functions combined together will return a numeric value. Because the RIGHT function in Excel always returns a text string, even if the original value is a number. If you use the VALUE function, the result will be a number. You can refer to the example of combining two functions in the article How to use the VALUE function in Excel.
Example 3: The RIGHT function outputs the character after a specific character
When you want to separate the character string following a specific character, you need to use the SEARCH function in Excel or the FIND function to determine the position of that character, but subtract the position of the selected character from the total character string returned by the function. WOOL. The combined formula is =RIGHT(text string, LEN(text string) – SEARCH(character, text string)).
With this example table, you need to find the name in the Full name box, enter the formula =RIGHT(B2,LEN(B2)-SEARCH(” “,B2)) and press Enter.
As a result, we get the name as shown below. Scroll down to display the remaining results.
By combining the above 3 functions, we can apply it to output a string of characters followed by any other character, such as comma, colon, hyphen,…
Example 4: The RIGHT function types the first n characters in the string
Recipe for use RIGHT(string, LEN(string)-number of chars to remove).
In this data table, the first 6 characters including dots will be removed from the character string. Enter the formula =RIGHT(B2, LEN(B2)-6) and press Enter. As a result, we get the result as shown below.
Example 5: The RIGHT function outputs the character after the last delimiter
With complex strings with many delimiters, content extraction must be separated from the last delimiter. In the data table below, there are cells that only use 1 delimiter, but there are cells that use 2 delimiters and require the character after the last delimiter.
Applying the formula to this data table, we enter =RIGHT(B6,LEN(B6)-SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6) ,”:”,””))))) and press Enter. In there:
- LEN(B6): total length of the character string in cell B6.
- LEN(SUBSTITUTE(B6,”:”,””)): length of character string without colons.
- LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””)): total original length minus the length of the character string without colons.
- SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””))): replace the last separating colon with the new character $, SUBSTITUTE function Allows replacement of specified character in string.
- SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””)))): determines the position of the last separator , in the image the colon is replaced by the $ sign.
- RIGHT(B6,LEN(B6)-SEARCH(“$”,SUBSTITUTE(B6,”:”,”$”,LEN(B6)-LEN(SUBSTITUTE(B6,”:”,””))))): returns the string to the right of the last delimiter, taking the total length of the string minus the position of the delimiter.
As a result, we get the character sequence as shown below.
Some frequently asked questions
How to delete characters using the RIGHT function in Excel?
The number of characters to be removed is subtracted from the total length of the string. For this, the following formula is used:
=RIGHT(string,LEN(string)-number_of_chars_to_remove)
- “string” is the entire source string.
- “number_of_chars_to_remove” contains the number of characters to remove from the source string.
How to use the RIGHT function for numbers in Excel?
The RIGHT function always returns a text string even if the source string is numeric. To get output as a number from a string of numbers, the RIGHT function is nested within the VALUE function.
The formula is stated as follows:
=VALUE(RIGHT(string, num_chars))
- “string” is the source string containing the numbers.
- “num_chars” is the number of digits extracted from the string.
Note: In Excel, number strings are right-aligned while text strings are left-aligned.
Why can't the RIGHT function work on dates?
Because the RIGHT Excel function is designed to handle text strings, dates are numbers in the internal Excel system. Therefore, the RIGHT function cannot retrieve an individual piece of data within a date such as day, month, or year. If you try to do this, you will only get the last few numbers representing the date.
RIGHT function in Excel not working? Cause and solution.
The RIGHT function in Excel does not work for many reasons:
- There are one or more spaces in the original data. In this case, you need to delete them using the TRIM function or the Cell Cleaner add-in.
- The num_chars argument is less than 0. Of course, you will want to put a negative number in the formula, but if num_chars is calculated by another Excel function or another combination of functions and the formula RIGHT returns an error, use a nested function to handle the problem.
- The original value is a date, so the RIGHT function cannot process this data.
Key ideas to remember
- The RIGHT function provides a specified number of characters from the right side of a text string.
- The RIGHT function extracts characters starting from the far right to the left.
- The RIGHT function accepts two arguments – “text” and “num_chars.”
- Default value of “num_chars” is set as 1.
- If “num_chars” greater than the length of the text, the RIGHT function returns the complete text.
- If “num_chars” is less than 0, the RIGHT function returns the error “#VALUE!”.
- The RIGHT function should not be used with numbers because it returns false values
with numbers formatted as text.
Wishing you success!