sort a column of numbers including minus numbers

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

--
Regards
Frank Kabel
Frankfurt, Germany


Rasoul Khoshravan Azar wrote:
> 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

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

On Sat, 27 Mar 2004 20:47:35 +0430, "Rasoul Khoshravan Azar"
<rasoulazar@hotmail.com> wrote:

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

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

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:s7cb60do4286ef8bgk0m0j9kquglnpm26i@4ax.com...
> 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
>
> On Sat, 27 Mar 2004 20:47:35 +0430, "Rasoul Khoshravan Azar"
> <rasoulazar@hotmail.com> wrote:
>
> >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
> >
>

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" <rasoulazar@hotmail.com> wrote ...
> 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.

> "Gord Dibben" <gorddibbATshawDOTca> wrote
> > I would move the minus sign to the left of the numbers.
> > [code provided was clipped]

Hi Rasoul

If you prefer a formula approach, you can find one below. There is also
a macro.
http://www.ozgrid.com/Excel/excel-im...ve-numbers.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

Dear Dave
Thanks for the link, It solved my problem. It was very neat with good
explanation.

Regards
Rasoul

"Dave Hawley" <dave@ozgrid.com> wrote in message
news:4066389b$0$201$75868355@news.frii.net...
> Hi Rasoul
>
> If you prefer a formula approach, you can find one below. There is also
> a macro.
> http://www.ozgrid.com/Excel/excel-im...ve-numbers.htm
>
> ***** Posted via: http://www.ozgrid.com
> Excel Templates, Training & Add-ins.
> Free Excel Forum http://www.ozgrid.com/forum *****