How to use Istext and Left together

[mschneider331]

I need to determine if the first three characters are text (use: Istext() and Left() ) In cell A4

[6StringJazzer]

Try this:

Please Login or Register  to view this content.

[sktneer]

You may try something like this....

Please Login or Register  to view this content.

[mschneider331]

it is saying "true" though on the cells that don't have the first 3 characters as letters. They are numbers. Microsoft Excel.jpg

[6StringJazzer]

I responded quickly before fully considering your question. I simply provided a formula based on the functions you identified without thinking about what you were actually trying to do. The ISTEXT function is poorly documented by Microsoft but seems to be intended to refer to a cell, even though you can put anything in there. In your case, you are referring to the result of the LEFT function. If that result is 123, it is a string, and ISTEXT will still interpret that as text.

I believe sktneer's formula is better, and this one should give the same result:

Please Login or Register  to view this content.

BTW your image attachment did not seem to work. Also, it's almost always better to just attach the Excel file.

[mschneider331]

Yes I've been reading stuff all over to understand it and not getting anywhere. That formula works, but what if we are only to use left and istext? There is no way to make those work?

[mschneider331]

Here is my sheet

[6StringJazzer]

...what if we are only to use left and istext? There is no way to make those work?

No.

LEFT returns a string. A string will always be interpreted by ISTEXT as text, even if it looks like a number. ISTEXT("123") returns TRUE. ISTEXT(LEFT(A4,3)) will always return TRUE.

You can force conversion of the result of LEFT to a number. If it's a number, then ISTEXT will return FALSE. But if it's not a number then the conversion produces a VALUE error, and ISTEXT will also return FALSE.

[XOR LX]

...and this one should give the same result:

Please Login or Register  to view this content.

This is a bit risky. If A4 contains, e.g. the text string "1E2", or "1/12", the above will return FALSE.

Regards

[SCOTPEO]

we should use value and is number to solve the problem.....

=isnumber(value(left(a4,3)))

hope this still help......