Do Anything with Text in Formulas
Same as Excel's built-in FIND 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 FIND function, itFINDR:
itFINDR is intended to be as similar as possible to Excel's built-in FIND function, while searching from right to left. For much more powerful and versatile searching, see itSEARCH.
|find_text||Required. The text to look
for in within_text.
Does not accept wildcard characters. All text in find_text is considered to be literal. For example, including an asterisk ("*") in find_text will look for a literal asterisk in within_text. See itSEARCH or itSEARCHR for wildcard-enabled searches.
|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.
|=itFINDR("b","abcde")||Find "b" in "abcde", and return the position counting from the right.||4|
|=itFINDR("bc","abcde")||Find "bc" in "abcde", and return the position of the last (rightmost) character in "bc", counting from the right.||3|
|=itFINDR("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.)
|=itFINDR("a","abcABC")||Find "a" (case sensitive) in "abcABC" and return the position counting from the right.||6
(The "A" in "abcABC" is ignored because itgFINDR is case sensitive. See itSEARCH or itSEARCHR for non-case sensitive searches.)
|Return the filename in the path in cell A1, no matter how many levels of folders are in the path.||MyFile.xls
(itFINDR 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")-itFINDR("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")-itFINDR("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