Table of Contents
In Microsoft Excel, users have the ability to search for a specific string of characters within a cell or range of cells. This can be done using the built-in “Find” function, but there may be times when the desired string is located on the right side of the cell. To search for a string from the right in Excel, users can use the “Find and Replace” feature and specify the direction of the search as “Search: By columns” in the “Options” menu. This will allow the user to locate the desired string from the right side of the cell, making it easier to find and manipulate data in Excel.
Excel: 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:
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:
=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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I search for a string from the right in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-search-for-a-string-from-the-right-in-excel/
stats writer. "How do I search for a string from the right in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-search-for-a-string-from-the-right-in-excel/.
stats writer. "How do I search for a string from the right in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-search-for-a-string-from-the-right-in-excel/.
stats writer (2024) 'How do I search for a string from the right in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-search-for-a-string-from-the-right-in-excel/.
[1] stats writer, "How do I search for a string from the right in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I search for a string from the right in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.