FormatCellsNumberText is very tricky!
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
|