Formatting issue

[Pete63]

I download files from our archaic business system in text format which I then import into Excel, when values are negative they are shown as xxx.xx-. The minus sign is on the right and I can't get the original file format changed.

Would anyone happen to know how I can quickly convert these to proper numbers in Excel?

Any help gratefully receieved

 

[Asala42]

Well, here's a formula approach.

=IF(RIGHT(A2,1)="-",-1*LEFT(A2,LEN(A2)-1),A2)+0

 

[Yogi Anand]

I download files from our archaic business system in text format which I then import into Excel, when values are negative they are shown as xxx.xx-. The minus sign is on the right and I can't get the original file format changed.

Would anyone happen to know how I can quickly convert these to proper numbers in Excel?

Any help gratefully receieved

Hi Pete63:

Following is my two step approach ...

1) select all the cells you want to fix, then do EDIT|Replace ... key-in - in the Replace_what box and nothing in the Replace_with box
At this point all the numbers will be converted into positive numbers

2) in a blank cell on the worksheet key-in -1, and then do EDIT|Copy ...
then select all the numbers where the - sign had been removed, and then do EDIT|PasteSpecial ... multiply

I hope this helps!

 

[Pete63]

Thanks

Asala42

Works a treat. Thanks for your help.

 

[Pete63]

Thanks also

Yogi Anand

Your technique works too. What can I say? You guys are amazing!