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?

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