MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing the way negative numbers are presented


Posted by Dwight on April 04, 2001 2:47 PM

I have a spreadsheet with columns of currency amounts negative dollar amounts are presented with a trailing minus sign (eg 25.75-) and positive amounts are presented in general format (eg 10.81). I want a macro to convert the columns of data to more conventional currency format.
Any ideas?


Posted by Bob on April 04, 2001 2:59 PM


Try selecting the column, right click, format cells, on the number tab...select accounting.

Posted by Barrie Davidson on April 04, 2001 3:09 PM

Is the data with the trailing negative sign recognized as text?
If yes, try putting the following formula in the column next to your data:
=IF(ISTEXT(A1),-VALUE(MID(A1,1,LEN(A1)-1)),A1)
where A1 is the cell address. Copy this formula down to the end of your data. Then select the all the formulas, copy them, and then paste value only. This should get your values.

Hope this helps, if not drop me an e-mail.

Posted by Aladin Akyurek on April 04, 2001 10:36 PM

See also:

11124.html

Posted by Dave Hawley on April 05, 2001 5:45 AM

Hi Dwight

As you asked for a macro I have adapted Barrys' formula. This will work on Column A


Sub ChangeFormat()
Dim TheRange As Range

Set TheRange = Columns(1).SpecialCells(xlConstants)

TheRange.Offset(0, 1) = _
"=IF(ISTEXT(RC[-1]),-VALUE(MID(RC[-1],1,LEN(RC[-1])-1)),RC[-1])"
TheRange.Offset(0, 1).Cut
TheRange = TheRange.Offset(0, 1).Value
TheRange.Offset(0, 1).Clear

Set TheRange = Nothing

End Sub


Dave


OzGrid Business Applications