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

Trisha Baker

New Member
Joined
Mar 23, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Select the data, Data > Text to Columns, click Next twice, tick Advanced, check Traiing Minus for Negative Numbers then click Finish.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,987
Messages
5,526,069
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top