sorting alphanumeric text

[Mike Harrison]

Is there any way of sorting alphanumeric text in a logical way. Example 1a, 2a, 3a, 11a, 11b, 12a would be logical but excel sorts them as 1a, 11a, 11b, 12a, 2a, 3a I want to be able to sort up to four starting digits followed by up to 3 following letters. I have been looking at the "code" function and the ascii values of numbers 0 through 9 as a possible method, but I can't figure out how to do it.

 

[Jim Cone]

Mike,

I would like to see somebody come up with the answer to your question.
The only thing I know to do is pad the number portion with zeros...
0001a
0002a
0003a
0011b
0012a
Hope that helps.

Jim Cone
San Francisco, CA

Is there any way of sorting alphanumeric text in a logical way. Example

1a, 2a, 3a, 11a, 11b, 12a would be logical but excel sorts them as 1a, 11a,
11b, 12a, 2a, 3a I want to be able to sort up to four starting digits
followed by up to 3 following letters. I have been looking at the "code"
function and the ascii values of numbers 0 through 9 as a possible method,
but I can't figure out how to do it.

 

[Bob Umlas, Excel MVP]

If your data starts in A1, enter this in B1 (or some empty column) via Ctrl/Shift/Enter
=1*LEFT(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW($1:$8),1)),0)-1
Then fill down, then sort A:B on column B. Then you can clear col. B

----- Mike Harrison wrote: ----

Is there any way of sorting alphanumeric text in a logical way. Example 1a, 2a, 3a, 11a, 11b, 12a would be logical but excel sorts them as 1a, 11a, 11b, 12a, 2a, 3a I want to be able to sort up to four starting digits followed by up to 3 following letters. I have been looking at the "code" function and the ascii values of numbers 0 through 9 as a possible method, but I can't figure out how to do it.

 

[Jason Morin]

One method is to use this array formula in the next column
over, fill down the column, select both columns, and sort
the data ascending on the formula column:

=LEFT(A1,MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(INDIRECT
("1:"&LEN(A1))),1)),0)-1)

Press ctrl/shift/enter after inserting the formula, then
fill down.

HTH
Jason
Atlanta, GA

-----Original Message-----
Is there any way of sorting alphanumeric text in a

logical way. Example 1a, 2a, 3a, 11a, 11b, 12a would be
logical but excel sorts them as 1a, 11a, 11b, 12a, 2a,
3a I want to be able to sort up to four starting digits
followed by up to 3 following letters. I have been looking
at the "code" function and the ascii values of numbers 0
through 9 as a possible method, but I can't figure out how
to do it.

 

[Mike Harrison]

Thank you all for responding. Both formulae work fine. I like Excel, use it frequently and am pleased with what I have accomplished with it. Your answers to what seems like such a simple problem, remind me once again that I have barely scratched the surface of the programs capabilities. Thank you again
Mike Harrison