Sorting by sequence , not value

[Emeng]

Hi all

Is there a way to sort by the first digit? E.G. 10,100,1000,1010,1020,110,20,200,210,220 etc.

Regards

Mark

 

[Faseeh]

Hi Emeng,


If you see closely, you will find that sorting nos by their first digit results in nos being arranged in ascending order!! Is't it that or i have misunderstood your question?


Regards,

 

[Emeng]

Hi again Faseeh

Maybe my header should read ‘Sorting By Accumulated Digit Value’.

I have a list of 124 numbers from 10 to 1240. I wish to use excel to load information into another program which sorts by adding the value of each digit. So 10, 100, 1000 have the same value. 10 has fewer digits (2) so is at the top of the list. Next are 1010,110, zero preceeds one, then 1100. It's unusual!

Regards

Mark

 

[Emeng]

Thinking out aloud....I wonder whether there is a way to arrange my data to suit the sort order of the other program. Copy the sort order into excel, paste my info alongside and then sort to match. Mark

 

[Debraj]

Hi Emeng,


If I understood your requirement.. if you change the number to TEXT and then sort.. It will work as per your requirement...

Create a helper column B,

in

B1 = Text(A1,"@")

Now sort the series according to B Column..

Choose Custom Sort >> Sort Numbers and Numbers stored as text Separately..


Regards,

Deb

 

[Emeng]

Hi Deb

You are a champion! Thanks very much.

Regards

Mark

 

[Phuzzy]

Why is this so hard. I have a column of numbers and mixed numbers with letters. I cannot, no matter how hard I try, sort this properly.


I have tried the text function with no success. I tried =text(a1,"@") and =text(a1,"#". I then copy the results and paste special values to sort, and they still sort out of order. Sample data below.


Help!


Excel 2003!


1

1

2

3

4

6

1A

4A

6A

4B


Would like

1

1

1A

2

3

4

4A

4B

6

6A

 

[Phuzzy]

Nevermind. Finally figured it out....


Had to format the column as text. Then do a Data/Sort... command.