Help with formula to return a number based on number string

[maym]

Hi

I have a bunch of numbers in a list like this:

45
13
123
123x
12x1
1x21
x213
1
5x13

I am trying to write a formula to determine if there is a 1 in the last 3 values of the cell, e.g. 123 would return 1 and 415 would return 1.

The problem I have is that some values contain the letter "x".

If the x is in the last place, I need to check the 3 values before this.
If the x is in the 2nd last place, I need to check the 2 values before this.
If the x is in the 3rd last place, I need to check the 1 value before this.
If the x is at the beginning, I need to check the numbers that appear after it.

If there are only 2 numbers in the cell, then the same applies (and it could also contain an "x").

Can anyone assist with this?

[arunsjain]

Hi,

You can use formula like

Please Login or Register  to view this content.

It will return 1 if there is 1 present in cell.

Cheers

[leelnich]

Hi all. Assuming values in column A:

Please Login or Register  to view this content.

...or perhaps just:

Please Login or Register  to view this content.

...if you don't need to restrict it to the last 3 characters.

[maym]

thank you, these are great.

lee - one of my cells has 4 values, "1234" so it returned 0. How can I adjust to return 1?

[arunsjain]

Hi Maym,

Try this one

Please Login or Register  to view this content.

It will give you 1 irrespective of digit in cell. If 1 present then you will get 1..

[leelnich]

Perhaps just:

Please Login or Register  to view this content.

...if you don't need to restrict it to the last 3 characters (in which case, the "x" may be ignored.)

[maym]

excellent, thank you both

[leelnich]

You're most welcome, thank you for the rep!