Excel: How to Search String from Right


You can use the following formula to search a string starting from the right in Excel:

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

This particular formula finds the last word in the string in cell A2.

This formula works by substituting each individual space in the string with 100 spaces, then by returning the last 100 characters in the string with leading and trailing spaces removed.

The result is that this formula simply returns the last word in a string. 

The following example shows how to use this formula in practice.

Example: How to Search String from Right in Excel

Suppose we have the following column of strings in Excel:

We can type the following formula into cell B2 to find the last word in the string in cell A2:

=TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 100)), 100))

We can then click and drag this formula down to each remaining cell in column B:

Excel search from right

Column B displays the last word in each string in column A.

If your strings have a delimiter other than a space, you can simply specify that delimiter in the SUBSTITUTE function.

For example, suppose each of our strings had slashes instead of spaces as delimiters:

We could type the following formula into cell B2 to find the last word in the string in cell A2:

=TRIM(RIGHT(SUBSTITUTE(A2, "/", REPT(" ", 100)), 100))

We can then click and drag this formula down to each remaining cell in column B:

Column B displays the last word that follows the last slash in the strings in column A.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Remove Specific Text from Cells
Excel: How to Remove Last 3 Characters from String
Excel: How to Insert a Character into a String

Leave a Reply

Your email address will not be published. Required fields are marked *