I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?

Hi Scott'

One way:

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

---
Regards,
Norman



"Scott" wrote in message
...
I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I
used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?

Assuming you have a mix of both positive and negative numbers, in the helper
column use this formula:

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


On Fri, 26 Nov 2004 08:33:03 -0800, "Scott"
wrote:

I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?

Scott

If using Excel 2002 or 2003........

DataText to ColumnsNextNextAdvanced Check "trailing minus for negative
numbers"

Macro............

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

OR for entire sheet...........

Sub Negsignleft()
Dim Cell As Range
Dim Rng As Range
''move minus sign from right to left on entire worksheet
On Error Resume Next
Set Rng = ActiveSheet.Cells. _
SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
For Each Cell In Rng
If IsNumeric(Cell.Value) Then
Cell.Value = CDbl(Cell.Value)
End If
Next Cell
End Sub

Gord Dibben Excel MVP

On Fri, 26 Nov 2004 08:33:03 -0800, "Scott"
wrote:

I have a data file I'm reformatting that has all the negative numbers with
the negative sign on the right side thereby Excel sees these as text. I used
a formula last week that converts these to the correct negative number but
seemed to have misplaced the formula.

Anyone help me with this trick?