Reformating USD in Excel to number..(saved from csv file) 1

[phospher]

Hi,

I have a csv file which I have saved as a xls file. The column showing the dollars can not be reformatted: Format,Cells,Number. I have 10k records and don't want to manually edit every record. Delete the USD at the end of every cell.

I tried to insert a new column and use the autosum to copy the $ 2,000.00 USD data over to the new column, but it reads it as $ 2,000.00 USD. Even after formating the new column to number with 2 decimal places.

Any help would be greatly appreciated.

Phospher

 

[bluedragon2]

Sounds likr it is seeing the $ 2,000.00 USD as a text field, so in the new column, try:

=VALUE(LEFT(A1,LEN(A1)-4))

sub the A1 for your Column.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

If that is the case, then just highlite that column and do an edit and replace. In the replace block, put <space>USD and in the replace with block, put nothing.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[phospher]

Thank-You Blue dragon2.

It worked like a charm. My purchasing group is most greatful as well.


Phos