sort a column of numbers including minus numbers

  • Thread starter Thread starter Rasoul Khoshravan Azar
  • Start date Start date
R

Rasoul Khoshravan Azar

Would like to sort a column of numbers including minus numbers.
However the result is odd. Numbers are sorted without considering the minu
sign. What is the problem.
Example:

0.41
3.58
5
0.17-
0.68-
0.75-
1.13-

but 5 should appear in top and then 3.58
 
Hi
probably your numbers are stored as text. Try converting them to
numbers:
- select your data
- goto 'data - Text in columns'
- go through this wizard and finish the wizard
- try your sorting again
 
Rashoul

I would move the minus sign to the left of the numbers.

If you have Excel 2002 you can do this by going to Data>Text to Columns>Fixed
Width>Next>Next and select the "Advanced" button. Checkmark in "trailing
minus for negative numbers" and Finish.

Now try your sort.

If you are using an earlier version of Excel you will need VBA to change the
signs.

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
 
I am using 2000 ver of Excel.
But data are stored in cells as numbers not as texts.
I will try the VBA code you have sent. Thanks for that.
TIA
Rasoul
 
Actually you may have formatted the column as number of
some kind but with a right minus you would find it is text
in your Excel 2000. =ISTEXT(A1) would show True so you would
need a macro such as Gord supplied or other means to convert
to a number. [ topic on my site is insrtrow.htm#fixrightminusown ]

Rasoul Khoshravan Azar said:
I am using 2000 ver of Excel.
But data are stored in cells as numbers not as texts.
I will try the VBA code you have sent. Thanks for that.
Click to expand...
Gord Dibben said:
I would move the minus sign to the left of the numbers.
[code provided was clipped]
Click to expand...
Click to expand...
 
Dear Dave
Thanks for the link, It solved my problem. It was very neat with good
explanation.

Regards
Rasoul
 
Back
Top