Convert text to number - whole column

[Lise]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Hello,
I have an excel file of > 5000 records, sent to me by a windows user, and the values are not recognized as such but as text (so filtering and formulas don't work). I can only convert 1 cell at a time, so selecting the entire column and then changing to number does not work unfortunately...
I hope I can sort this out, thank you so much in advance for helping !
Liselotte, Belgium

 

[Carl Witthoft]

Are the cells formatted as text, or do they have a " ' " in front of
the numbers? If the latter, doing a global Find&Replace will solve
your problem.

If the former, what happens when you select an entire column and choose
a Number format from the menu?

 

[Jim Gordon Mac MVP]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Hello,
I have an excel file of > 5000 records, sent to me by a windows user,
and the values are not recognized as such but as text (so filtering and
formulas don't work). I can only convert 1 cell at a time, so selecting
the entire column and then changing to number does not work
unfortunately...
I hope I can sort this out, thank you so much in advance for helping !
Liselotte, Belgium

One way to fix this problem is to copy the cells that are formatted as
text and paste them elsewhere using Paste Special > Values.

-Jim

 

[Lise]

Thank you jim, I have tried so, but unfortunately, it stays formatted as text. I have tried all paste special possibilities...

 

[CyberTaz]

Hi Lise;

You might try entering the digit 1 into a cell, copy it, then select the
column of figures. Use Edit> Paste Special - Multiply [in the Operations
group], OK.

If that still doesn't get the job done there must be something else about
the content of the cells that you aren't recognizing ‹ spaces, non-digit
characters ‹ that are preventing that content from being recognized as
values. In Excel 2008, even when the cell is formatted as Text or the
apostrophe (') precedes the digits the content can still be used in
calculations as long as the characters in the cell can be parsed as a value.

HTH |:>)
Bob Jones
[MVP] Office:Mac