Excel how to take characters in the cell

Question

I would like to return the characters after "OR XXXXXXXXXXXX" in the reference cell. Rather than using a RIGHT function, I think there is an way to set up an way with "LEN" functions. Here is what I have found so far-

Cell A2:

=IF(
ISERR(SEARCH("LUR",C2))=FALSE,
RIGHT(B2, 10),
C3)

But this isn't work for Cell A2, A3 or so on. Is there any way I can extract only the characters after "OR" in Column B? Thanks!

Answer 1

You can still use RIGHT(), but you make the second parameter based on the LEN() and Find() function:

=RIGHT(B2, LEN(B2) - FIND("OR", B2, 1)-1)

FIND() returns the starting position of the characters "OR" in B2. Substracting that from the length minus 1 more gets us to the right number of characters for the second parameter.