Formatting negative numbers

  • Thread starter Thread starter Matthew McManus
  • Start date Start date
M

Matthew McManus

Is it possible to format cells so that 53- is read as the number -53?

And if so, is it also possible to format cells so that both -53 and 53
are read as -53?

Thanks
Matthe
 
Matthew

If you are running XL2002 or 2003 you can do this through Data>Text to Columns

Select the range of cells then Data>Text to Columns>Fixed>Next>Advanced> check
"trailing minus for negative numbers">Finish.

If earlier version of Excel, you cannot Format, you have to switch the sign
using VBA.

Sub ChangeSign()
Dim cell As Range
''to move a - sign from right to left in Column A
On Error Resume Next
For Each cell In Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp)). _
SpecialCells(xlConstants, xlTextValues)
If Right(Trim(cell.Value), 1) = "-" Then
cell.Value = CDbl(cell.Value)
End If
Next
On Error GoTo 0
End Sub

Gord Dibben Excel MVP
 
In earlier versions, if you wish, you can also change existing data with
trailing negatives "after the fact" without using code if you don't mind
using a "helper" Column.

If data starts in A1, insert a column and enter this:

=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1)*-1,A1)

Copy down as needed.
 
Back
Top