sorting a text column
 

How do I sort a text column which includes A to Z then AA to AZ then BA to BZ
....

AHMTD,

For a list starting in cell A1, and extending down column A, you will need
two helper columns. In cell B1, use the formula

=LEN(A1)

In cell C1, use the formula

=LEFT(A1,1)

and copy both those cells down to match your list.

Then select all three columns, and sort first based on Col B Ascending, then
Col C Ascending, and finally, Col A ascending.

HTH,
Bernie
MS Excel MVP


"AHMTD" wrote in message
...
How do I sort a text column which includes A to Z then AA to AZ then BA to
BZ
...

If you will be doing this often, you can create a "custom list" which will
enable you to do a simple sort using options.

Create your list in a column, then select the entire list, then.
<Tools <Options <CustomLists

Since your list is already selected, just click on the "Import" button, then
<OK.

Now, when it comes time to sort, click the "options" button in the Sort
window,
Choose your list in the key sort window,
Then <OK <OK
And you're done !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
AHMTD,

For a list starting in cell A1, and extending down column A, you will need
two helper columns. In cell B1, use the formula

=LEN(A1)

In cell C1, use the formula

=LEFT(A1,1)

and copy both those cells down to match your list.

Then select all three columns, and sort first based on Col B Ascending, then
Col C Ascending, and finally, Col A ascending.

HTH,
Bernie
MS Excel MVP


"AHMTD" wrote in message
...
How do I sort a text column which includes A to Z then AA to AZ then BA to
BZ
...

Your answer was very helpful for me.
But it is not possible to do that operation for second or third sort
orders. (I looked the help, it was not possible).
Thank you
"RagDyeR" wrote:

If you will be doing this often, you can create a "custom list" which will
enable you to do a simple sort using options.

Create your list in a column, then select the entire list, then.
<Tools <Options <CustomLists

Since your list is already selected, just click on the "Import" button, then
<OK.

Now, when it comes time to sort, click the "options" button in the Sort
window,
Choose your list in the key sort window,
Then <OK <OK
And you're done !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
AHMTD,

For a list starting in cell A1, and extending down column A, you will need
two helper columns. In cell B1, use the formula

=LEN(A1)

In cell C1, use the formula

=LEFT(A1,1)

and copy both those cells down to match your list.

Then select all three columns, and sort first based on Col B Ascending, then
Col C Ascending, and finally, Col A ascending.

HTH,
Bernie
MS Excel MVP


"AHMTD" wrote in message
...
How do I sort a text column which includes A to Z then AA to AZ then BA to
BZ
...

*SURE* you can ... just takes a little more effort.

Same procedure as if you wanted to sort on 5 keys, when XL has only 3
choices by default.

Since the "CustomList" can only be used for the first sort key, you'll have
to repeat the sort process as many times as you have sort orders, and you
must do them in reverse, from minor to major.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"AHMTD" wrote in message
...
Your answer was very helpful for me.
But it is not possible to do that operation for second or third sort
orders. (I looked the help, it was not possible).
Thank you
"RagDyeR" wrote:

If you will be doing this often, you can create a "custom list" which
will
enable you to do a simple sort using options.

Create your list in a column, then select the entire list, then.
<Tools <Options <CustomLists

Since your list is already selected, just click on the "Import" button,
then
<OK.

Now, when it comes time to sort, click the "options" button in the Sort
window,
Choose your list in the key sort window,
Then <OK <OK
And you're done !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
AHMTD,

For a list starting in cell A1, and extending down column A, you will
need
two helper columns. In cell B1, use the formula

=LEN(A1)

In cell C1, use the formula

=LEFT(A1,1)

and copy both those cells down to match your list.

Then select all three columns, and sort first based on Col B Ascending,
then
Col C Ascending, and finally, Col A ascending.

HTH,
Bernie
MS Excel MVP


"AHMTD" wrote in message
...
How do I sort a text column which includes A to Z then AA to AZ then
BA to
BZ
...