Cannot Format or Calculate Cell Data 2

[AllenRitch]

I have a spreadsheet that was developed by exporting from a database (I don't have access to this database and cannot have the information re-ran).

One of the columns has currency data, however, I cannot add or change the format. An example of the data within a cell appears as $45,652.32 when I view it's contents from the formula bar.

I've tried copying the data into a new column using several different methods (e.g. paste specail), but the only thing that works is double clicking within the cell and then hiting enter. Doing so somehow changes the data and removes the dollar signs and commas. I can then manipulate. But seeing how there's over 2,500 rows, I'm hoping there's an easier way.

Thanks for any help you might could provide.

 

[Lilliabeth]

Select all 2500 rows
Click in the formula bar
Hit Ctrl+Enter

 

[AllenRitch]

That did work, however, there are different dollar values in each cell. Doing the above changed all the dollar values to equal the main cell highlighted.

 

[AnotherHiggins]

Take comfort in the fact that this is a common problem with imported data.

The standard fix is called the 'Times 1 Fix' and it goes like this:

[ul][li]to the right of all the imported data, type 1 (this should be right-aligned, indicating that the cell is formatted as a number)[/li]
[li]copy that cell[/li]
[li]select the range containing the cells that are giving you trouble[/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now format the column containing the dollar amounts as you wish[/li][/ul]

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[AllenRitch]

Yep...that worked. I merely multiplied each cell value times 1 in a new column and it did the trick.

Thanks.

 

[Lilliabeth]

Sorry. When you said you had done the paste special, I thought you had tried multiply and it had not worked

 

[AnotherHiggins]

->I merely multiplied each cell value times 1 in a new column and it did the trick.

Another way to do the same thing if you are copying and pasting into a new cell:
Copy data
In an empty column, Edit > Paste Special > Add.

Adding the text to zeros (the null cells) forces the data to number format and leaves the values the same.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.