# Cell Holds a Number but Does not Evaluate to a Number

• 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?

• 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))

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!