How to use Excel RIGHT function – Formula Examples

How to get a substring that comes after a certain character

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:

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

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:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2))

The result will look similar to this:

How to extract a substring after the last occurrence of the delimiter

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:

1. Get the number of delimiters in the original string. It’s an easy part:
   
   • Firstly, you calculate the total length of the string using the LEN function: LEN(A2)
   • Secondly, you compute the length of the string without delimiters by using the SUBSTITUTE function that replaces all occurrences of a colon with nothing: LEN(SUBSTITUTE(A2,”:”,””))
   • Finally, you subtract the length of the original string without delimiters from the total string length: LEN(A2)-LEN(SUBSTITUTE(A2,”:”,””))

   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.

2. Replace the last delimiter with some unique character. In order to extract the text that comes after the last delimiter in the string, we need to “mark” that final occurrence of the delimiter in some way. For this, let’s replace the last occurrence of a colon with a character that does not appear anywhere in the original strings, for example with a pound sign (#).
   

   If you are familiar with the syntax of the Excel SUBSTITUTE function, you may remember that it has the 4^th 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:

   =SUBSTITUTE(A2,":","#",LEN(A2)-LEN(SUBSTITUTE(A2,":","")))

   If you put this formula in a separate cell, it would return this string: ERROR:432#Connection timed out

3. Get the position of the last delimiter in the string. Depending on what character you replaced the last delimiter with, use either case-insensitive SEARCH or case-sensitive FIND to determine the position of that character in the string. We replaced the last colon with the # sign, so we use the following formula to find out its position:
   

   =SEARCH("#", SUBSTITUTE(A2,":","#",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))

   In this example, the formula returns 10, which is the position of # in the replaced string.

4. Return a substring to the right of the last delimiter. Now that you know the position of the last delimiter in a string, all you have to do is subtract that number from the total string length, and get the RIGHT function to return that many characters from the end of the original string:
   

   =RIGHT(A2,LEN(A2)-SEARCH("$",SUBSTITUTE(A2,":","$",LEN(A2)-LEN(SUBSTITUTE(A2,":","")))))

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:

=IFERROR(RIGHT(A2,LEN(A2)-SEARCH("$",SUBSTITUTE(A2,":","$",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))), A2)

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:

How to remove the first N characters from a string

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:

=RIGHT(A2, LEN(A2)-6)