This is a common problem with imported data. The cause is that the data is formatted as text. Even though you might have tried to change the formatting, even though you can check the formatting of the cells and Excel says they are formatted as number,
they are really still formatted as text.
Following are two solutions. Either will coerce all cells in the column without you having to 'tickle' every individual cell:
[tab]
Text to Columns
[ul][li]Select the column containing the offending data[/li]
[li]Go to
Data > Text to Columns[/li]
[li]Choose either
Fixed Width or
Delimited[/li]
[li]Press
Next[/li]
[ul][li]If you selected
Delimited, uncheck all options[/li]
[li]If you selected
Fixed Width, double click on any column breaks Excel tries to put in[/li]
[ul][li]Either way, the important thing here is to ensure that you do not see any column breaks in the
Data Preview[/li][/ul][/ul]
[li]Press
Next[/li]
[li]In the top right corner, select
General[/li]
[li]Press
Finish[/li]
[/ul]
[tab]
Times One Fix
[ul][li]In a cell to the right of all the imported data, type
[blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble
[red]*[/red][/li]
[li]Go to
Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]
[red]*[/red] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.
[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read
FAQ 181-2886 before posting.
