Excel Sorting Order

[DoxieLvr]

Is there a way in Excel to set up a sort so that the sort is done by digit?

The following list of numbers
2024
2032
2008
2016
20008
2020
20020
20032
2012

when sorted ascending would typically appear sort like this
2008
2012
2016
2020
2024
2032
20008
20020
20032

The desired sort is this
20008
20020
20032
2008
2012
2016
2020
2024
2032


Help!!!!

 

[FSt1]

hi
excel's sort is fixed (and by some opinions...limited) and thers's not much
you can do about it. special sorts outside of excel default usually requires
a helper column (and sometimes more than one). based on your example data
(and i'm not sure what you mean by "digit"), you want the "big" numbers
sorted above the "smaller" numbers.
???? correct???
or is there something else i'm not catching on to ?????
uh.....add a helper column and enter this formula into it....
=if(len(a2)>4,0,1)
copy down.
assuming that your data is in column A, sort by helper column then column A.
adjust ranges above if needed.

if i missed something, please provilde more details.

regards
FSt1

 

[Fred Smith]

What you are asking for is a simple text sort, in ascending order. As long
as your cells are text, Excel will sort the way you want.

Options are:
-- format the cells to text before you enter data
-- convert the cells to text using Paste Special...
-- Insert a helper column with the formula =text(a1,"0"), then sort on this.

Regards,
Fred

 

[Gord Dibben]

Data assumed to be in column A

In B1 enter =MID(A1,3,2)

Copy down.

Sort A and B on column B

Select "Sort anything that looks like a number as a number"


Gord Dibben MS Excel MVP