Sorting alphanumeric

Joanne · 04-26-2005, 05:06 PM

I would like to sort some text which is formatted in column one as:
1'
100
1000'
2
2'
and so on. Any number that does not have an "'" at the end should come
before any number that does and all the 1s, 2s, 3s, etc. should be grouped
together. Is this possible? The result would look like:
1
100
1000'
2
2'

Boy, would I appreciate some help.

**MWE** · 04-26-2005, 10:56 PM

Originally Posted by Joanne

I would like to sort some text which is formatted in column one as:
1'
100
1000'
2
2'
and so on. Any number that does not have an "'" at the end should come
before any number that does and all the 1s, 2s, 3s, etc. should be grouped
together. Is this possible? The result would look like:
1
100
1000'
2
2'

Boy, would I appreciate some help.

The unsorted and sorted data in your example are not the same. In the unsorted, the first item is 1' That seems to have changed to 1 in the sorted data.

given that the unsorted data is correct, the sorted should be
100
1'
1000'
2
2'

correct?

This can be done with a VBA macro. Attached is a zipped spreadsheet containing some sample data and a macro to sort it. Just click on the Yellow Button and see if it does what you want

Bill Kuunders · 04-27-2005, 07:06 PM

Column A has the numbers to sort.

enter in B1 =LEFT(A1)
enter in C1 =IF(RIGHT(A1)="'","1",A1)
enter in D1 =LEFT(A1,LEFT(LEN(A1)-1))

extend formula's down the columns
highlight the area and select data sort
sort by column B, then by column C, then by column D.

Come back to us with more samples if this does not work for you.
--
Greetings from New Zealand
Bill K

"Joanne" <[email protected]> wrote in message
news:[email protected]...
>I would like to sort some text which is formatted in column one as:
> 1'
> 100
> 1000'
> 2
> 2'
> and so on. Any number that does not have an "'" at the end should come
> before any number that does and all the 1s, 2s, 3s, etc. should be grouped
> together. Is this possible? The result would look like:
> 1
> 100
> 1000'
> 2
> 2'
>
> Boy, would I appreciate some help.

Joanne · 04-28-2005, 01:06 PM

Thank you very much for your help. It's almost working. I neglected to say
that there was text in column two, but I just moved all of the formulas over
one column, assuming that would work. ( I entered the formulas and then
sorted by columns, C, D, E). The results I got were that the number 17' in
column one came out at the top of the list, even before the number 1. The
other "'" came out after the regular numbers. Thanks for any help you can
provide.

"Joanne" wrote:

> I would like to sort some text which is formatted in column one as:
> 1'
> 100
> 1000'
> 2
> 2'
> and so on. Any number that does not have an "'" at the end should come
> before any number that does and all the 1s, 2s, 3s, etc. should be grouped
> together. Is this possible? The result would look like:
> 1
> 100
> 1000'
> 2
> 2'
>
> Boy, would I appreciate some help.

Bill Kuunders · 04-28-2005, 06:06 PM

Joanne,
Change the if formula to read as below (10000 in stead of 1)
to get the "'"numbers last.
=IF(RIGHT(B1)="'","10000",B1)

Can you send more data as examples to sort if this is still not right.
If you do get the question to sort as numbers or to sort numbers and text
seperately
choose the top option .....sort as numbers.
Regards
--
Greetings from New Zealand
Bill K

"Joanne" <[email protected]> wrote in message
news:[email protected]...
> Thank you very much for your help. It's almost working. I neglected to
> say
> that there was text in column two, but I just moved all of the formulas
> over
> one column, assuming that would work. ( I entered the formulas and then
> sorted by columns, C, D, E). The results I got were that the number 17'
> in
> column one came out at the top of the list, even before the number 1. The
> other "'" came out after the regular numbers. Thanks for any help you can
> provide.
>
> "Joanne" wrote:
>
>> I would like to sort some text which is formatted in column one as:
>> 1'
>> 100
>> 1000'
>> 2
>> 2'
>> and so on. Any number that does not have an "'" at the end should come
>> before any number that does and all the 1s, 2s, 3s, etc. should be
>> grouped
>> together. Is this possible? The result would look like:
>> 1
>> 100
>> 1000'
>> 2
>> 2'
>>
>> Boy, would I appreciate some help.