Text column to number column

[goblin]

Can anyone tell me what exactly Excel uses to distinguish a normal cell from a cell storing text. Property name, flag name, anything. I just need to know how Excel knows!!! :D

I have a workbook exported from a database and some of the columns have numberical values stored as text. I'm trying to compare against one of those columns using the worksheet function MATCH, and it refuses to match the 'numbers stored as text' column to anything entered as numbers. And I have no idea how to enter numbers as text (formatting them as text does not seem to do the trick )

The only way I've found to get everything to work is to do a 'Text to columns' on the columns that are stored as text. Is there a simpler way?

Goblin

 

[DonkeyOte]

try using match with a value(cell_ref) or your text to column data.

eg match(value(a1),Sheet1!A:A,0)

this will convert the text '1 to a value of 1.

 

[Andrew Poulsom]

If the "numbers" in your list (A1:A6) are text entries and your lookup cell (B1) contains a number, you can use:

=MATCH(TEXT(B1,"0"),A1:A6,0)

 

[goblin]

Works like a charm! :D Never knew there was a worksheet function like that!

Thanks,
Goblin