Negative numbers

  • Thread starter Thread starter Blake Witten
  • Start date Start date
B

Blake Witten

I need to be able to import negative numbers, but when
they have right hyphens, they are considered text not
negative numbers.

I've tried to change the "regional settings" for negative
signs, but it's made no difference.

What can I do?

Thanks,

Blake
 
Blake

During the import, on the last panel there is an "advanced" button. Click that and you'll see a check box for trailing minus signs

Ar

----- Blake Witten wrote: ----

I need to be able to import negative numbers, but when
they have right hyphens, they are considered text not
negative numbers

I've tried to change the "regional settings" for negative
signs, but it's made no difference

What can I do

Thanks

Blake
 
Blake

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 or a helper column with this formula.

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.

VBA Method follows.................

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