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

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

    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!