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

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

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

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

If you use Format | Cells | Alignment then choose General for
horizontal alignment, a numerical 2006 will align to the right, whereas
a text 2006 will appear left aligned. This is easier to see if you
widen the column, and the technique is particularly useful when
importing dates.

You can make your SP formula accept both 2006 and "2006" by multiplying
by 1 or incorporating VALUE( ).

Hope this helps.

Epinn wrote:

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

Hi Pete,

Thank you for dropping by. I was thinking the other day that I haven't "run into" you for a while.

Yes, I think I have to add 0 to 2006 or use VALUE( ) so that SP can work consistently.

Epinn

"Pete_UK" wrote in message ups.com...
If you use Format | Cells | Alignment then choose General for
horizontal alignment, a numerical 2006 will align to the right, whereas
a text 2006 will appear left aligned. This is easier to see if you
widen the column, and the technique is particularly useful when
importing dates.

You can make your SP formula accept both 2006 and "2006" by multiplying
by 1 or incorporating VALUE( ).

Hope this helps.

Epinn wrote:

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

Hi Epinn,

I was on holiday in Canada for a couple of weeks, and since then most
of the threads seem to be answered by the time they are displayed in
Google Groups, so I've not posted very many this month.

Nice to "talk" to you again.

By the way, I use XL2000 which doesn't have green triangles, so that is
not a very reliable method for checking for text !! <bg

Pete

Epinn wrote:

Hi Pete,

Thank you for dropping by. I was thinking the other day that I haven't "run into" you for a while.

Yes, I think I have to add 0 to 2006 or use VALUE( ) so that SP can work consistently.

Epinn

"Pete_UK" wrote in message ups.com...
If you use Format | Cells | Alignment then choose General for
horizontal alignment, a numerical 2006 will align to the right, whereas
a text 2006 will appear left aligned. This is easier to see if you
widen the column, and the technique is particularly useful when
importing dates.

You can make your SP formula accept both 2006 and "2006" by multiplying
by 1 or incorporating VALUE( ).

Hope this helps.

Epinn wrote:

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