Imported negative values

[glen.e.mettler]

Using 2003
I have imported data with numeric negative values. I have formated the

column to numeric data. However, the value does not evaluate to
negative - ie - A5 = -10. When I select another cell and enter = A5<0
It should evaluate to TRUE however it evaluates to FALSE. If I
manually reenter the data as -10, it evaluates to TRUE.

I don't want to manually reenter all the data. How can I get Excel to
"see" this as a proper negative number?


Glen

 

[aidan.heritage]

I would guess that the cells got confused when the import happened - if
you run a macro that does the following


sub update
for each cell in range("A1:A2000")
'change the range to whatever you need
cell.value=cell.value
'looks slightly silly, but fixes the problem
next
end sub

 

[Jim Thomlinson]

Often When you import data the numbers will come through as text. Here is
some code that I use to convert text to numbers. It runs on whatever range
you have selected.

Private Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

On Error Resume Next
Application.ScreenUpdating = False
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) <> "=" Then

If IsNumeric(rngCurrent.Value) Then
rngCurrent.NumberFormat = "0"
rngCurrent.Value = CDbl(rngCurrent.Value)
rngCurrent.Formula = rngCurrent.Value
End If
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

select the column

format the column as General (format=>Cells=>Numberformat, choose general)

then do Data=>Text to Columns, select delimited and select Tab as the
delimiter.

this should cause excel to reevaluate the formula and treat them as numbers.
If that is not successful, there is a good chance you have embedded spaces
in your cells or non breaking spaces or something similar. Then you would
probably want to use a macro to clean that up.