- on wrong side of number (negative number)

[mlchman]

I get a report from work every month that has been converted from an Adobe PDF report to Excel. At line 59 the - for negative is on the right side of the numbers instead of the left side. Excel reads this as text, so will not format as currency. How do I change/reformat so I can use the data as currency in my calculations?

Thanks for the help.

Michael

 

[Scott Huish]

Hi, welcome to the board!

Is it always in Row 59 and in what column? Is there more than 1 value like this?

 

[Tazguy37]

Welcome to MrExcel Board!

You can convert the whole column to numbers with a formula like this:
=IF(NOT(ISNUMBER(--RIGHT(A1))),--LEFT(A1,LEN(A1)-1),A1)

and copy down; adjust the column (A) to wherever your numbers are. Data, Text to Columns might also work for this.

Hope that helps!

 

[Brian from Maui]

Have a look at ASAP Utilities

www.asap-utilities.com

 

[Peter_SSs]

mlchman

Welcome to the Mr Excel board!

Here are three other possible ways, take special note of 3 as it is the easiest (provided you have an appropriate version of Excel):

1. =IF(RIGHT(A1)="-",-LEFT(A1,LEN(A1)-1),A1)

2. =IF(RIGHT(A1)="-",-SUBSTITUTE(A1,"-",""),A1)

3. If you want to do it without creating another column and are using Excel 2002 or later:
a) Select the cells (or columns) that contain the numbers
b) Data|Text to Columns...
c) Next|Next|Advanced
d) Ensure 'Trailing minus for negative numbers' is checked and click OK
e) Finish
NOTE: If you know the 'Trailing minus for negative numbers' is checked, then you only need to perform steps a), b) and e) - very easy!

 

[mlchman]

Thank you for all the replys. With the info I received, I am now able to correct the problem easily.

Thanks again,

Mlchman