SEARCH - but from Right to Left...

[Aitch]

I have data in a column which looks something like this:

1X3456789 - SEARCH ("X" , A1) = 2
234X73876 - SEARCH ("X" , A2) = 4
839030X38 - SEARCH ("X" , A3) = 7

Is there a way to SEARCH in reverse so it looks from Right to Left ??

1X3456789 - reverseSEARCH ("X" , A1) = 8
234X73876 - reverseSEARCH ("X" , A2) = 6
839030X38 - reverseSEARCH ("X" , A3) = 3


Thank you for any help!

 

[Eric W]

This will get you the position of the rightmost "X":

Book2
	A	B
1	1X3456X89	7
2	234XX3876	5
3	23X76X35X	9
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=IFERROR(AGGREGATE(14,6,ROW(INDIRECT("1:"&LEN(A1)))/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="X"),1),"")

If you want the second "X" from the right, change the 1 near the end of the formula to 2. If you want to find the nth "X" from the LEFT, change the 14 near the start to a 15.

 

[Rick Rothstein]

Here is another formula that you can consider...

=IF(COUNTIF(A1,"*X*"),1+LEN(TRIM(RIGHT(SUBSTITUTE(A1,"X",REPT(" ",99)),99))),"")

 

[Peter_SSs]

.. and another one

Edit: Oops, sorry, I thought the OP had Excel 365 but I see that is not so. Still I'll leave it in case use for others.

20 08 04.xlsm
	A	B
1	1X3456X89	3
2	234XX3876	5
3	23X76X35X	1
Reverse search

Cell Formulas
Range	Formula
B1:B3	B1	=MATCH(88,CODE(RIGHT(A1,SEQUENCE(99))),0)

 

[Peter_SSs]

So if not Excel 365, a couple of other options.
May require Ctrl+Shift+Enter confirmation in other Excel versions.
One is volatile but string can be any length
The other is not volatile but only deals with strings up to 9 characters (which is what all the OP's samples were)

20 08 04.xlsm
	A	B	C
1	1X3456X89	3	3
2	234XX3876	5	5
3	23X76X35X	1	1
Reverse search

Cell Formulas
Range	Formula
B1:B3	B1	=MATCH(88,CODE(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))),0)
C1:C3	C1	=MATCH(88,CODE(RIGHT(A1,{1,2,3,4,5,6,7,8,9})),0)

 

[Aitch]

These work great! (Hopefully I'll figure out how they work at some point lol!)

Much appreciated guys!

 

[Fluff]

Glad we could help & thanks for the feedback.