PDA

View Full Version : Changing cells' format



vosmerkin
04-11-2008, 01:09 AM
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
04-11-2008, 01:14 AM
Change it to '5, a leading apostrophe.

vosmerkin
04-11-2008, 03:12 AM
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
04-11-2008, 03:19 AM
okay, so explain what you mean by '... treated like a string ...'. you can do that with a number, so what is the problem?

vosmerkin
04-11-2008, 03:31 AM
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
04-11-2008, 04:59 AM
Try this

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

vosmerkin
04-11-2008, 05:27 AM
Works. Thanks
Can you explain why?

Bob Phillips
04-11-2008, 05:43 AM
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
04-13-2008, 10:29 PM
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