The green triangle is not reliable - only good for integers and decimals. A fraction like 1 1/4 will show the triangle while a fraction like 1/4 won't show it.

Is there a visual way to tell if the value is text?

Epinn

"Epinn" wrote in message ...
Thank you for your response. This is more confusing than I have expected ......

Any number that is left aligned is not necessarily text as we can always click the "Align Left" button.
Now, I have found another layer.
I think the green triangle is more telling i.e. if I switch the feature on.

Epinn

"Gary''s Student" wrote in message ...
That is because the format as Text only asserts once data has been entered.
Try the following:

1. in an un-formatted cell (say A1) enter 1
2. format A1 as Text
3. =ISTEXT(A1) will still show FALSE !
4. now enter 2 in cell A1
5 the 2 appears as text and ISTEXT now shows TRUE !!

Strange & marvelous are the ways of Excel
--
Gary's Student


"Epinn" wrote:

Hi all,

This is something basic and I have only found out today. Oh well, I am still a newbie.

Scenario 1

If I format a brand new blank cell as TEXT and key in a number say 2006, the format is text. ISTEXT( ) returns TRUE.

Scenario 2

If I key in 2006 to a brand new blank cell which has a default format of general and then format the cell as TEXT, the format is NOT text. ISTEXT( ) returns FALSE. I have always been under the impression that by formatting a cell to text regardless of when, I have got text.

This revelation is important to formula writing.

For example, in my SUMPRODUCT formula I have to check for "2006" (with quotes) for scenario 1 if I want a match; I have to check for 2006 (without quotes) for scenario 2.

COUNTIF doesn't care if I have quotes or not for both scenarios.

So, formatting a cell to text *after* I have keyed in a number doesn't make it text?

Experts, I appreciate your comments on my "discovery" as I feel that I may still have missed a piece of the puzzle. Also, please let me know if you think of any other similar subtleties.

Thanks.

Epinn