PrecisionCalc
inspector text
Do Anything with Text in Formulas

 

itSEARCHR

Same as Excel's built-in SEARCH function, but searches from right to left, and returns the position number counting 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.

If you want to search from right to left, but return the position number of the string found counting from left to right, see the last examples below.

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.

Wildcard Characters:

  • You can use the wildcard characters "?" (question mark) and "*" (asterisk) in find_text.
    • The question mark finds any single character.
    • The asterisk finds any string of characters of any length, including no characters.
  • To search for a literal question mark or asterisk, precede it with a tilde ("~") in find_text.
    • To search for a literal question mark, use "~?" in find_text.
    • To search for a literal asterisk, use "~*" in find_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)
 
  A
1  C:\MyFolder\MyFile.xls
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.)
=LEN("abcdabcda")-itSEARCHR("b","abcdabcda")+1 Find the first "b" in "abcdabcda" searching from the right, BUT return the position number of that "b" counting from the LEFT. 6
(This is the same as searching from the left for the LAST "b", and returning its position.)

(Note that this only works if find_text is a single character! To do this where find_text is more than one character, see next example, below.)
=LEN("abcdabcda")-LEN("bcd")-itSEARCHR("bcd","abcdabcda")+2 Find the first "bcd" in "abcdabcda" searching from the right, BUT return the position number the first character in that "bcd" counting from the LEFT. 6
(This is the same as searching from the left for the LAST "bcd", and returning the position of its first character.)

 

 

PrecisionCalc Home Page