excel formula if cell contains

[BORUCH]

hi

i would like if someone can provide me with a formula that checks a cell if it contains a lowercase x if it does then ... if not then ...

Another condition for the if statement is that the lowercase x must be proceeded by a number any number

Thanks

 

[jasonb75]

Perhaps something like

=ISNUMBER(MID(A1,FIND("|",SUBSTITUTE(A1,"x","|"))-1,1)+0)

This will not work if there is more than one lower case x in the string and the first one is not preceded by a number.

 

[BORUCH]

Perhaps something like

=ISNUMBER(MID(A1,FIND("|",SUBSTITUTE(A1,"x","|"))-1,1)+0)

This will not work if there is more than one lower case x in the string and the first one is not preceded by a number.

ok sorry for not being clear enough, another thing it has to check is that its only allowed to exits once the lower case x, it cannot exits more then once in the cel. UPPER CASE i don't care

 

[jasonb75]

Try

=ISNUMBER(IF((LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))=1,MID(A1,FIND("|",SUBSTITUTE(A1,"x","|"))-1,1)+0,""))

There may be other factors to take into consideration that will not become obvious until the formula is tested.

 

[BORUCH]

Try

=ISNUMBER(IF((LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))=1,MID(A1,FIND("|",SUBSTITUTE(A1,"x","|"))-1,1)+0,""))

There may be other factors to take into consideration that will not become obvious until the formula is tested.

Thank you very much