Sorting hyphenated numbers

Connie Martin · 01-20-2005, 01:06 PM

How do you sort a list of hyphenated and non-hyphenated numbers so that
2079-1 comes before 2121?

Connie Martin

**swatsp0p** · 01-20-2005, 01:52 PM

IMHO, you will need to ensure all entries are text (as hyphenated numbers will be treated as text). You will then need to separate the hyphened number away from the base number (use Text to Columns with the ' - ' as the delimiter). This will give you two columns of numbers, base numbers in one, hyphened in the next. Sort your range using the now base numbers only column as the Sort By column.

So, assuming your list of numbers is in column D, insert columns E-F. Text to Columns into E & F, sort with E as your Sort-By column (add any additional columns related to this data). Then Delete columns E & F

HTH

Max · 01-20-2005, 02:06 PM

Try sorting the list on an adjacent helper col

Assuming the list is in col A, A1 down

Put in B1: =TEXT(A1,"@")
Copy down

Then sort both cols A and B by col B > Ascending

This should return the results ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Connie Martin" <[email protected]> wrote in message
news:[email protected]...
> How do you sort a list of hyphenated and non-hyphenated numbers so that
> 2079-1 comes before 2121?
>
> Connie Martin

**swatsp0p** · 01-20-2005, 02:59 PM

Max: your formula does make sure all is text, however, Excel sorts text in alpha order, and entries without a hyphen will *always* come before text with a hyphen.

To meet the request of the poster, removing the '-' and trailing characters is (to the best of my knowledge) imperative.

e.g.: (all as text)

2121
1234-0
2079-1
12-340
1-2340
123-40
9854

will Sort>Ascending as:

2121
9854
1-2340
12-340
123-40
1234-0
2079-1

My method, Text to Columns gives:

2121 2121
9854 9854
1-2340 1 2340
12-340 12 340
123-40 123 40
1234-0 1234 0
2079-1 2079 1

After sorting by second column:

1-2340 1 2340
12-340 12 340
123-40 123 40
1234-0 1234 0
2079-1 2079 1
2121 2121
9854 9854

Deleting the 'helper' columns leaves data in desired order (i.e. 2079-1 is before 2121).

HTH

Connie Martin · 01-20-2005, 03:06 PM

Thank you. That works. I put the column outside the print area. I was
hoping there would be a list one could create and then sort by it, but I
guess not.

Thanks again
Connie Martin

"Max" wrote:

> Try sorting the list on an adjacent helper col
>
> Assuming the list is in col A, A1 down
>
> Put in B1: =TEXT(A1,"@")
> Copy down
>
> Then sort both cols A and B by col B > Ascending
>
> This should return the results ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1Â° 22' N 103Â° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Connie Martin" <[email protected]> wrote in message
> news:[email protected]...
> > How do you sort a list of hyphenated and non-hyphenated numbers so that
> > 2079-1 comes before 2121?
> >
> > Connie Martin
>
>
>

Max · 01-20-2005, 08:06 PM

You're welcome !
Thanks for posting back
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Connie Martin" <[email protected]> wrote in message
news:[email protected]...
> Thank you. That works. I put the column outside the print area. I was
> hoping there would be a list one could create and then sort by it, but I
> guess not.
>
> Thanks again
> Connie Martin