alphanumeric sorting

[Mike]

I got this data in a column:

100A
100B
100C
10A
10B
45A
45B
45C
4B
4D

and I need to sort it so it comes out this way:
4B
4D
10A
10B
45A
45B
45C
100A
100B
100C

Anyone know how I can do this?

Regards
Mike

 

[Ron Rosenfeld]

I got this data in a column:

100A
100B
100C
10A
10B
45A
45B
45C
4B
4D

and I need to sort it so it comes out this way:
4B
4D
10A
10B
45A
45B
45C
100A
100B
100C

Anyone know how I can do this?

Regards
Mike

Set up an adjacent "helper column".

Extract just the numeric portion of your data to go into this helper column.
You can do this using the formula:

=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH(
{0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),
ROW(INDIRECT("1:"&LEN(A1)))))

(substitute the address of the first cell in your column for A1, and fill
down).

Select both columns (or a larger range if necessary).

Sort ascending
First by the "helper column"
Then by the original data column.


--ron

 

[Erny]

Hi Mike,

I would add a column temporarily to the right with the following formula
(suppose your first value is in cell A1 and you added a column B). In cell
B1 I enter:

=VALUE(LEFT(A1,LENGTH(A1)-1))

and replicate it to all cells where I have values in the first column;

Next, I would sort the 2 columns A and B by selecting as first item column B
and next item column A (both ascending).
Finally, once data is sorted, I would delete the temporary column B.

Kind regards,
Erny

 

[David McRitchie]

Hi Mike,
I don't see your original post except within Emy's reply.
Expect that your data is not just simply all have a single
letter suffix as you have represented. Take a look at
http://www.mvps.org/dmcritchie/excel/sorting.htm