Changing cells' format

[vosmerkin]

Hello
Simetimes I have such a problem. For example I have numeric value "5" in a cell. But I want it to be treated like string. I change format to text, but its not a text yet. It becomes a text string only after editing this cell.
Is this normal or some bug in Excel?
Im using Office2003 on WinXP

[Bob Phillips]

Change it to '5, a leading apostrophe.

[vosmerkin]

It's not the way. If entering apostroph then I'll better stand on each cell and put F2 and Enter. But I have already few tables with numbers that should be treated like strings. It's not a problem to do this with 100-200 cells.

I'm interested how to avoid this problem in future. If this behavior is needed for some reason - then let it be. But if it's not normal then I'd like to know how to fix it.
Thanks

[Bob Phillips]

okay, so explain what you mean by '... treated like a string ...'. you can do that with a number, so what is the problem?

[vosmerkin]

Formatted like string
=IF(A1=A2;1;0)

this formula shows 0 if cells have different format. (A1 - string; A2 - number). After changing A2 formatting to text it still doesn't work. Select A2, press F2 and Enter. After this formula shows 1

[Bob Phillips]

Try this

=IF(--A1=--A2;1;0)

[vosmerkin]

Works. Thanks
Can you explain why?

[Bob Phillips]

Basically, it is coercing a text value to its numeric equivalent. So, if you have text 5, it coerces it to numeric 5. Then you you are just comparing numbers to numbers.

Note it will error on strings.

[vosmerkin]

So I cant compare cells as strings. It's a pitty. Cause I can have "0050" and "50" and this are different.
But thanks anyway