I have 2 cells B1 and C1. They both show a value of 1002. They are both
formatted as text fields. The LEN function returns 4 for each cell. I do a
=CODE(LEFT(B1,1)) and get a value of 49. The same for cell C1. I check the
Middle 2 chars and the last character and they match exactly. BUT the
=IF(B1=C1,1,2) returns a 2 - the cells don't match. B1 shows a green
triangle in the upper left so I think it's related to the format but I'm
stumped. Thanks for any help you can provide.

That's the way it is, real nums < text nums
Instead of: =IF(B1=C1,1,2)
You could use: =IF(B1+0=C1+0,1,2) as the test
where the +0 will coerce text nums, if any, to real nums for correct
numerical computation. Source real nums will not be impacted in value by the
"+0".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"dhstein" wrote:
I have 2 cells B1 and C1. They both show a value of 1002. They are both
formatted as text fields. The LEN function returns 4 for each cell. I do a
=CODE(LEFT(B1,1)) and get a value of 49. The same for cell C1. I check the
Middle 2 chars and the last character and they match exactly. BUT the
=IF(B1=C1,1,2) returns a 2 - the cells don't match. B1 shows a green
triangle in the upper left so I think it's related to the format but I'm
stumped. Thanks for any help you can provide.

One must be entered as '1002 which makes it text
the other as 1002 which makes it a number hence not the same...

This will pass the tests you perfomed.

If you click on the green arrow it will tell you that 'Number stored as text'

"dhstein" wrote:

I have 2 cells B1 and C1. They both show a value of 1002. They are both
formatted as text fields. The LEN function returns 4 for each cell. I do a
=CODE(LEFT(B1,1)) and get a value of 49. The same for cell C1. I check the
Middle 2 chars and the last character and they match exactly. BUT the
=IF(B1=C1,1,2) returns a 2 - the cells don't match. B1 shows a green
triangle in the upper left so I think it's related to the format but I'm
stumped. Thanks for any help you can provide.