Help with extracting second last number from string

Hi

I have a list of numbers that vary in length and also contain the letter "x" in some of them like this:

0x60
1313
0x41
x254
123x
1
9x73
00x9
522x
335
x39x
34


And so on. I need to do 3 things:

1. Extract the last number only if it is a "1". If it is an "x", then I need to extract the second last number.
2. Extract the last number if it equals 1,2 or 3. If it is an "x", then I need to extract the second last number.
3. Extract the last number if there is an occurrence of 1 in the last 3. If there is only 1 or 2 numbers in the cell, then look for an occurrence of 1 in these and extract the last number.

Can anyone assist me with this please?

Thanks

Originally Posted by maym

Hi
0x60
1313
0x41
x254
123x
1
9x73
00x9
522x
335
x39x
34

What exactly your desired results of above list, one by one?

Try in B2:
=IF(RIGHT(SUBSTITUTE(A2,"x",""))+0=1,1,"")
C2:
=IF(OR(RIGHT(SUBSTITUTE(A2,"x",""))+0={1,2,3}),RIGHT(SUBSTITUTE(A2,"x",""))+0,"")
D2:
=IF(ISNUMBER(SEARCH(1,RIGHT(A2,3))),1,"")
Drag all down
There is slightly difference with 1 in A7: 2nd result is 1 (your derived result for 2nd result is nothing)

Could you clarify this:
123x -->D6: second result: 2
9x73-->D8: second result : 2

Originally Posted by maym

thank you so much!

For 3rd result for A6 (in cell D6), I get nothing. However there was an occurrence of 1 in the last 3 (even though it was before the x) so I should get a 1 here?

Use:
=IF(ISNUMBER(SEARCH(1,RIGHT(SUBSTITUTE(A2,"x",""),3))),1,"")

Note that with this, after "x" is moved out, 1 is in last 3 digits then
1x39x --> 3rd = 1