Occasionally I run into the problem where a cell has a simple number in it, say 123456 yet when I compare it to another cell with the same value it says they are not the same. Upon testing the cells in various ways I have found he the 1st cell does not evaluate to a number while the second one correctly does.. The column that the 1st cell is in is formatted as text. The cell above the offending cell also is a 6 digit number in a text column, say 654321. That cell does evaluate to a number. If I copy and paste the 654321 cell down to the cell holding the 123456 the original offending cell then evaluates to a number. When I then over-type the pasted cell back to 123456 it does not. When I change the column format from text to general the cell evaluates as a number correctly but this is not always optimal for the column to be formatted other than as text. Ideas?
Cell Holds a Number but Does not Evaluate to a Number
If the cell formatted as text is A1 and the cell formatted as number (General) is B1, then you could use VALUE(A1)=B1. If either cell could be formatted as text, then use VALUE(A1)=VALUE(B1).
Thank you for your response, A version of your response would work perfectly. I think I have resolved the issue using the flip side of that coin.
The problem is that sometimes Excel would see the entry in a given column as a number and sometimes as text.
My application calls for a use of combined INDEX and MATCH functions. MATCH was not functioning because of the variability in the way a number was presented.
I added in IF, ISERROR, and TEXT statements to check if the two cells agree and if not to convert one of the cells into a text format to match the other.
Just for laughs, the formula that works is IF(ISERROR(MATCH($C4,A:A,0))=FALSE,MATCH($C4,A:A,0),MATCH(TEXT($C4,"#"),A:A,0)).
Ace! For an issue like that, another answer is to format all the cells as text.
Also, if you have a version newer then 2003, you can shorten that to =IFERROR(MATCH($C4,A:A,0),MATCH($C4&"",A:A,0))
Don’t have an account yet? Register yourself now and be a part of our community!