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:
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
