Formula doesn't apply condition for excluding numbers because cell is stored as text

[Tupelo1984]

Hi all,

I have two queries:

1/I am trying to figure out how I can make my formula take in consideration an exclusion if my cell contains a string of numbers (in this case if my cell contains 00000000) which is stored as text:
The formula below works fine when my cell is a cell stored as number. The objective is to return a result 0 if the cell contains 00000000

Results stored as text:

=IF(AND(COUNT(0/FIND('Quality Rules'!$C$2:$C$14,O2))),"0",IF(AND(COUNTIFS(O2,"<>.",O2,"<>*-*",O2,"<>00000000")),IF(AND(LEN(O2)>5,LEN(O2)<15),1,0)))

Results stored as number : example with 11111111

=IF(AND(COUNT(0/FIND('Quality Rules'!$C$2:$C$14,O2))),"0",IF(AND(COUNTIFS(O2,"<>.",O2,"<>*-*",O2,"<>111111111")),IF(AND(LEN(O2)>5,LEN(O2)<15),1,0)))

I can't use the text to columns function to reformat to number as I need the original entry to stay in my cell (00000000 would reformat to 0)

2/Additionally, I am not able to figure out to make the formula return 0 instead of FALSE

Thanks a lot for your help!

 

[pratikjoshi448]

Try using the ISNUMBER() condition. Use the desired if-else to generate the result. I hope this works.