In the last few articles, we've discussed different Text functions - those that are used to manipulate text strings. Today our focus is on the RIGHT function, which is designed to return a specified number of characters from the rightmost side of a string. Like other Excel Text functions, RIGHT is very simple and straightforward, nevertheless it has a few unobvious uses that might prove helpful in your work.
The RIGHT function in Excel returns the specified number of characters from the end of a text string.
The syntax of the RIGHT function is as follows:
For example, to extract the last 3 characters from the string in cell A2, use this formula:
The result might look something similar to this:
Important note! The Excel RIGHT function always returns a text string, even if the original value is a number. To force a Right formula to output a number, use it in combination with the VALUE function as demonstrated in this example.
In real-life worksheets, the Excel RIGHT function is rarely used on its own. In most cases, you will be using it together with other Excel functions as part of more complex formulas.
In case you want to extract a substring that follows a specific character, use either SEARCH or FIND function to determine the position of that character, subtract the position from the total string length returned by the LEN function, and pull that many characters from the rightmost side of the original string.
Let's say, cell A2 contains the first and last name separated by a space, and you aim to pull the last name to another cell. Just take the generic formula above and you put A2 in place of string, and " " (space) in pace of character:
The formula will yield the following result:
In a similar manner, you can get a substring that follows any other character, e.g. a comma, semicolon, hyphen, etc. For example, to extract a substring that comes after a hyphen, use this formula:
The result will look similar to this:
When dealing with complex strings that contain several occurrences of the same delimiter, you may often need to retrieve the text to the right of the last delimiter occurrence. To make things easier to understand, have a look at the following source data and desired result:
As you can see in the screenshot above, Column A contains a list of errors. Your goal is to pull the error description that comes after the last colon in each string. An additional complication is that the original strings may contain different numbers of delimiter instances, e.g. A3 contains 3 colons while A5 just one.
The key to finding a solution is determine the position of the last delimiter in the source string (the last occurrence of a colon in this example). To do this, you will need to use a handful of different functions:
To make sure the formula works right, you can enter it in a separate cell, and the result will be 2, which is the number of colons in cell A2.
If you are familiar with the syntax of the Excel SUBSTITUTE function, you may remember that it has the 4th optional argument (instance_num) that allows replacing only a specific occurrence of the specified character. And since we have already calculated the number of delimiters in the string, simply supply the above function in the fourth argument of another SUBSTITUTE function:
If you put this formula in a separate cell, it would return this string: ERROR:432#Connection timed out
In this example, the formula returns 10, which is the position of # in the replaced string.
As shown in the screenshot below, the formula works perfectly:
If you are working with a large dataset where different cells may contain different delimiters, you may want to enclose the above formula in the IFERROR function to prevent possible errors:
In case a certain string does not contain a single occurrence of the specified delimiter, the original string will be returned, like in row 6 in the screenshot below:
Apart from extracting a substring from the end of a string, the Excel RIGHT function comes in handy in situations when you want to remove a certain number of characters from the beginning of the string.
In the dataset used in the previous example, you may want to remove the word "ERROR" that appears at the start of each string and leave only the error number and description. To have it done, subtract the number of characters to be removed from the total string length, and supply that number to the num_chars argument of the Excel RIGHT function:
In this example, we remove the first 6 characters (5 letters and a colon) from the text string in A2, so our formula goes as follows:
As mentioned in the beginning of this tutorial, the RIGHT function in Excel always returns a text string even if the original value is a number. But what if you work with a numeric dataset and want the output to be numeric too? An easy workaround is nesting a Right formula in the VALUE function, which is specially designed to convert a string representing a number to a number.
For example, to pull the last 5 characters (zip code) from the string in A2 and convert the extracted characters to a number, use this formula:
The screenshot below shows the result - please notice the right-aligning numbers in column B, as opposed to left-aligned text strings in column A:
Since the Excel RIGHT function is designed to work with text strings whereas dates are represented by numbers in the internal Excel system, a Right formula is unable to retrieve an individual part of a date such as a day, month or year. If you attempt to do this, all you will get is a few last digits of the number representing a date.
Supposing, you have the date 18-Jan-2017 in cell A1. If you try to extract the year with the formula RIGHT(A1,4), the result would be 2753, which is the last 4 digits of number 42753 that represents January 18, 2017 in the Excel system.
"So, how do I retrieve a certain part of a date?", you may ask me. By using one of the following functions:
The following screenshot shows the results:
If your dates are represented by text strings, which is often the case when you export data from an external source, nothing prevents you from using the RIGHT function to pull the last few characters in the string that represent a certain part of the date:
If a Right formula does not work right in your worksheet, most likely it's because of one of the following reasons:
This is how you use the RIGHT function in Excel. To have a closer look at the formulas discussed in this tutorial, you are most welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week.
Excel RIGHT function - examples (.xlsx file)
Table of contents