Hi Connie
Really, I thought Excel could do better

You are being a bit harsh.
Excel is following absolutely normal rules for sorting.
The first sort is correct for the data that you have entered. The second
sort is correct for what is an entirely different data set.
All my formula was doing was making up for the fact that all data was
not entered in a consistent manner to start with.

If you find it easier to enter the -0 with each data entry, then that's
great. There is a standard pattern and Excel (or any other sorting
routine) will get it correct.

--
Regards

Roger Govier


"Connie Martin" wrote in
message ...
Sorry, I didn't see the entire post here until now. I tried the
second
formula and that works, however, I may as well enter the numbers with
a -0 to
start with and then I don't need a helper column with a formula. If
this is
the best Excel can do with this type of sort, I guess that's what I
will have
to do. Really, I thought Excel could do better. Thank you. Connie

"Roger Govier" wrote:

Hi Connie

You needed to change the formula to
=IF(LEN(A2)=4,A2&"-1",A2)

It will only add a "-1" to cells which are 4 characters in length.
Are you saying that some cells have numbers like 12345?

It worked fine for me and gave the correct sort order with your data
sample.

Perhaps a revision to
=IF(ISNUMBER(FIND("-",A2)),A2,A2&"-0")

This way it doesn't matter about the length of the numbers, if there
is
already a hyphen in the cell, it will repeat it as is, if not then
add
"-0" to the number, as there will not be any revisions of 0 in the
list.
Sort on this helper column, then delete the helper column,

--
Regards

Roger Govier


"Connie Martin" wrote in
message ...
My spreadsheet data starts at A2, so I changed all the three A1's
in
your
formula to A2 in the helper column, but what it does is simply
add -1
to all
my numbers. Also, some numbers have -2, -3 and so on. The way
Excel
sorts
such a list by default is that 1382 would come before 1085-1. I
don't
want
that. The -1, -2, etc., indicate a revision to the original
document
and
indicate whether first, second, third revision, etc. Connie


"Roger Govier" wrote:

Hi Connie

I can't figure a way to do with Custom Lists, but you could use a
helper
column.
In the helper column enter
=IF(LEN(A1)=4,A1&"-1",A1)
and copy down

Mark your data including the helper column, and sort by the helper
column.
After sorting, delete the helper column.

--
Regards

Roger Govier


"Connie Martin" wrote in
message ...
I want to create a new custom list in Tools/Options that will
sort a
list of
numbers with dashes in this way:

1383
1769-7
1794-8
1849-1
1913
1957
1962-1
1977-3
1996-1
2009-2
2020-1
2051
2077-2
2079-2
2102-1
2121-1
2126-1
2129-1
2130-1
2135
2139
2161-2
2169-2
2179
2180

How do I have to set up my custom list so it sorts like this?

Connie