Hi

I have a column of numbers as below and want to sort numerically so that all
the 02002's follow on from each other but can't seem to get this to happen
all numbers that have -## at the end are sorting to the bottom - what am i
doing wrong please?
02002
02010
03001
03002
02002-01
02002-02

--
Thanks

Lise

This sort-by-helper col easy tinker might suffice for you
Assuming data as posted in A2 down
In B2: =LEFT(A2,5)+0
Copy down. Select both cols A and B, sort by col B, ascending.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
"Lise" wrote:
I have a column of numbers as below and want to sort numerically so that all
the 02002's follow on from each other but can't seem to get this to happen
all numbers that have -## at the end are sorting to the bottom - what am i
doing wrong please?
02002
02010
03001
03002
02002-01
02002-02

Hi,

You are doing nothing wrong, this is the correct sort order. You have a mix
of text and numbers. Convert everthing to text and then sort:

1. Suppose your items are in A1:A10, in B1 enter the formula =LEFT(A1,20)
and fill it down. Copy the formulas, and choose Edit, Paste Special, Values.
2. Rearrange the data in the order you want it sorted, manually. The
choose Tools, Options, Custom List, Import, Close.
3. Select a list you want to sort and choose Data, Sort, Options, open the
First key sort order drop down and pick you custom list. Click OK.

If you only need to sort once this is probably not a great options.

If this helps, please click the Yes button.

--
Thanks,
Shane Devenshire


"Lise" wrote:

Hi

I have a column of numbers as below and want to sort numerically so that all
the 02002's follow on from each other but can't seem to get this to happen
all numbers that have -## at the end are sorting to the bottom - what am i
doing wrong please?
02002
02010
03001
03002
02002-01
02002-02

--
Thanks

Lise

Thank you both - I was pulling my hair out but all good now. Excellent
--
Thanks

Lise


"ShaneDevenshire" wrote:

Hi,

You are doing nothing wrong, this is the correct sort order. You have a mix
of text and numbers. Convert everthing to text and then sort:

1. Suppose your items are in A1:A10, in B1 enter the formula =LEFT(A1,20)
and fill it down. Copy the formulas, and choose Edit, Paste Special, Values.
2. Rearrange the data in the order you want it sorted, manually. The
choose Tools, Options, Custom List, Import, Close.
3. Select a list you want to sort and choose Data, Sort, Options, open the
First key sort order drop down and pick you custom list. Click OK.

If you only need to sort once this is probably not a great options.

If this helps, please click the Yes button.

--
Thanks,
Shane Devenshire


"Lise" wrote:

Hi

I have a column of numbers as below and want to sort numerically so that all
the 02002's follow on from each other but can't seem to get this to happen
all numbers that have -## at the end are sorting to the bottom - what am i
doing wrong please?
02002
02010
03001
03002
02002-01
02002-02

--
Thanks

Lise

Welcome, Lise. Which alternative did you go with?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500, Files:362, Subscribers:62
xdemechanik
---
"Lise" wrote in message
...
Thank you both - I was pulling my hair out but all good now. Excellent
--
Thanks

Lise