Converting text to numbers

S

Scott

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?
 
M

Myrna Larson

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)
 
G

Gord Dibben

Scott

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

Data>Text to Columns>Next>Next>Advanced 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
 
Top