=LEFT(D2,LEN(D2)-1)*-1 Moving Negative to Left side of Column

[Trisha Baker]

I have a report that was downloaded from PFW to excel, but the negative $ amounts are on the right side ie., -157.10 is downloaded in excel as 157.10-. When I use the formula =LEFT(D2,LEN(D2)-1)*-1 as per the help field in excel, it turns the 0.00 amounts to #VALUE! and the positive number to negative numbers. I would like to just formatt the column instead of adding a column with this formula as well. Any ideas?

Thanks - Trish

 

[Jonmo1]

Welcome to the board...

Well, formatting the cell is going to do no good (even if it were possible).
Even if you could format it to move the - to the left, the value in the cell will remain unchanged...not a real number in excel.

Try

=IF(RIGHT(D2)="-",-LEFT(D2,LEN(D2)-1),D2)

 

[VoG]

Select the data, Data > Text to Columns, click Next twice, tick Advanced, check Traiing Minus for Negative Numbers then click Finish.

 

[Jonmo1]

Select the data, Data > Text to Columns, click Next twice, tick Advanced, check Traiing Minus for Negative Numbers then click Finish.

Yeah....
That's much better...

 

[Trisha Baker]

Awesome - You both made a Temp person look like a princess. Thank you so much!!!

 

[scottylad2]

Awesome - You both made a Temp person look like a princess. Thank you so much!!!

so what are you when your not a person?