Log In
Register
Home
Excel Tables
Excel Expert
Power Query
Power Pivot
September 21, 2023
Offline
I would like to return TRUE/FALSE if a number includes a "1" (TRUE) or "11" (FALSE).
I'm finding it hard to figure out a suitable formula to achieve this that caters for numbers with more other numerals. e.g. "111", "1196", "19611".
See attached spreadsheet as an example.
Is there a formula that would cater for this please?
Moderators
January 31, 2022
Offline
I've entered a formula in the table that produces 0 or 1 in line with the required result FALSE or TRUE. Though, it also returns 0 (i.e. FALSE) if there is no "1" and no "11". For example "234" returns 0.
See attached.
EDIT: Forget it, I just found a mistake. The number "10" will also return FALSE but I believe you want it to be TRUE as it has "1" in it. Or not?
Trusted Members
October 17, 2018
Offline
I was working on the same idea, I added two columns CHECK "1" and CHECK "11"
Take a look, the desired outcome I have done nothing to that so you can figure out your own logic
Excel Tables
Excel Expert
Power Query
Power Pivot
September 21, 2023
Offline
Hey,
thanks for your replies.
I realised that if there is a 1, 111 etc, the number of 1's is odd. if 11, that is even.
I ended up with =ISODD(LEN(B2)-LEN(SUBSTITUTE(B2,"1","")))
That works a treat!
1 Guest(s)
