Green triangle - text fields don't match.

D

dhstein

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.
 
M

Max

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
 
S

Sheeloo

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'
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Non matching text data of the same values 3
What is small green triangle in upper left corner of cell? 6
Green Triangle in Cell 4
How do I ignore the "Number stored as text" error message permane. 3
How do I eliminate the green triangle throughout my spreadsheet? 2
What does a small green triangle in the upper left hand corner of. 3
Ignoring blank cells while locating common text value 1
How do I eliminate the green triangle throughout my spreadsheet? 4

Top