inspector
text
Find It. Control It.
You're In Charge.
itSEARCHR
Same as Excel's built-in SEARCH function, but searches from right to left.
Locates one text string within a second text string, and returns the number of the ending (rightmost) position of the first text string from the last character of the second text string.
Like Excel's built-in SEARCH function, itSEARCHR:
itSEARCHR is intended to be as similar as possible to Excel's built-in SEARCH function, while searching from right to left. For much more powerful and versatile searching, see itSEARCH.
Syntax
itSEARCHR(find_text,within_text,start_num)
| find_text | Required. The text to look for in within_text. |
| within_text | Required. The text in which to look for find_text. |
| start_num | The character at which to
start the search, starting from the right. The rightmost character in
within_text is 1. If start_num is omitted, the search begins at the rightmost character in within_text. |
Examples
| Formula | Description | Result | ||||
| =itSEARCHR("b","abcde") | Find "b" in "abcde", and return the position counting from the right. | 4 | ||||
| =itSEARCHR("bc","abcde") | Find "bc" in "abcde", and return the position of the last (rightmost) character in "bc", counting from the right. | 3 | ||||
| =itSEARCHR("a?c","abcabbc") | Find "a", than any single character, then "c", in "abcabbc", and return the position of the last (rightmost) character in "abcabbc", counting from the right. | 5 | ||||
| =itSEARCHR("a*d","abcde") | Find "a", then any number of any characters, then "d", in "abcde", and return the position of the last (rightmost) character in "abcde", counting from the right. | 2 | ||||
| =itSEARCHR("a~?c","a?cabc") | Find "a?c" in "a?cabc", and return the position of the last (rightmost) character in "a?cabc", counting from the right. | 4 (The tilde "~" makes the following wildcard character ("?") literal, so that it searches for the actual text "a?c". See also itFINDR.) |
||||
| =itSEARCHR("ab","abcabc",3) | Find "ab" in "abcabc", looking from the right starting with the 3rd character in "abcabc" from the right, and return the last (rightmost) character in "ab", counting from the right. | 5 (The search starts with the 2nd "a" ("abcabc", because it is the 3rd character from the right. Looking to the left from there, the next "ab" found is the first one ("abcabc"). Counting from the right, those two characters are in positions 5 and 6, and the rightmost character of them ("b"), is in position 5.) |
||||
| =itSEARCHR("a","abcABC") | Find "a" (not case sensitive) in "abcABC" and return the position counting from the right. | 3 (The "A" in "abcABC" is found because itgSEARCHR is not case sensitive. See itFINDR or itSEARCH for case sensitive searches.) |
||||
=RIGHT(A1,itSEARCHR("\",A1)-1)
|
Return the filename in the path in cell A1, no matter how many levels of folders are in the path. | MyFile.xls (itSEARCHR finds the position of the last backslash, which is 11. Excel's built-in RIGHT function subtracts 1 from that and gets the resulting number of characters (10 characters) from the right-hand side of the text. Those 10 characters are the filename in the path.) |