Problem with MID function

[Tornado1981]

Hi,


I'm trying to check if the first integer in A1 is greater than "3" then B1="OK" else B1="Not OK"


This is the function i'm using but B1 is always "OK"


Any help please ??

B1=IF(MID(A1,1,1)>3,"OK","Not OK")

 

[Rick Rothstein]

Hi,


I'm trying to check if the first integer in A1 is greater than "3" then B1="OK" else B1="Not OK"


This is the function i'm using but B1 is always "OK"

B1=IF(MID(A1,1,1)>3,"OK","Not OK")

The MID function returns text, not numbers, and text is alway greater than numbers, hence your results. The key is to involve the numerical text in a mathematical operation which forces Excel to convert the text number to a real number...

=IF(1*MID(A1,1,1)>3,"OK","Not OK")

The multiplication by one converts the digit to a number without changing its value. You could also add zero to the expression in place of the multiplication and that would work too (you could also prefix the MID function with two minus signs and that would also work).

 

[norulen]

Try this
=IF(LEFT(A1,1)*1>3,"OK","Not OK")
or
=IF(MID(A1,1,1)*1>3,"OK","Not OK")

 

[mikerickson]

Another approach is to test against strings rather than numbers. That helps prevent error values being returned if the first character in A1 is non-numeric.

=IF(AND("3" < MID(A1,1,1), MID(A1,1,1)<="9"), "ok", "not")

 

[Tornado1981]

thank u sooo much

 

[T. Valko]

Hi,


I'm trying to check if the first integer in A1 is greater than "3" then B1="OK" else B1="Not OK"


This is the function i'm using but B1 is always "OK"


Any help please ??

B1=IF(MID(A1,1,1)>3,"OK","Not OK")

Here's another one.

=IF(--LEFT(A1)<4,"Not ","")&"OK"

Assumes the cell will only contain integers.