Formatting negative numbers

  • Thread starter Matthew McManus
  • 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
 
G

Gord Dibben

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
 
R

Ragdyer

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.
 
Top