Not really homework, but thought I would try asking here:

How would I sort a mix of alphanumeric and numeric cells? I want them to sort ascending by alphanumeric, then ascending by numeric.

Ex: A20, A6, A100, 1234, 23, 3457 are the example values

I want them sorted
A6
A20
A100
23
1234
3457


But I cannot achieve this with the standard sorting options. Any ideas?

so only the leading characters are letters? eg 43A9 and A40B4 are not valid?

im not big on excel, so i'm afraid i can't be too specific. i assume there's a function that will pull regex matches. the idea is to create 2 new columns, one with just the letters and one with just the numbers. then sort first by letters, then secondary sort by numbers.

to pull the letters you can use a regex like "[a-zA-Z]+" and the numbers "\d+"

if you're familiar with VBA you can do the same thing with code. but if there's a function/formula/whateveritscalled, you can do something like =REGEXMATCH(A1, "[a-zA-Z]+") then you're golden.

This post was edited by carteblanche on Apr 5 2017 08:07pm

How long is the string?
There's always something u can do

This post was edited by Penguins0690 on Apr 5 2017 11:29pm

I would use an IFERROR statement combined with a MID.

Example

Column A (beginning at cell A2)
A6
A20
A100
23
1234
3457

Column B
=IFERROR(A2/1,NUMBERVALUE(MID(A2,2,5)))
Copy this formula down thru the bottom of your list. What this formula does is check to see if there is a numerical division possible between the cell and any number number (for the example, 1). Any cell with an alpha char in it will error out forcing it to use the "else" function. This combines a MID function taking the values of the cell beginning with the 2nd character listed and converts it to a numbervalue (if we leave this out it will stay a text value, we don't want this). If you only have 1 alpha character maximum then this will work fine, if not we need to nest an additional iferror in there.

Column B will now look like this
6
20
100
23
1234
3457

Now you can copy column B into C (or D) and paste VALUES (right click, paste special, values). Then simply sort values ascending as you wish.

Take a look
http://imgur.com/a/rO1MX

This post was edited by Petro on Apr 6 2017 08:16am

Thanks guys. I tried the different methods and was able to get the desired sorting order.

Glad it worked out for you. Let me know if you need any other excel help

Spent 2 hours thinking this through. I don't know all the excel functions so I was googling up stuff.

Here's a really stupid way to do it, but it'll do it. Furthermore, it'll handle any amount of letters and numbers. Since you're better off copy/pasting the list into a program outside of excel, and bringing back to excel (unless if there's an excel function).

First column: Your data
Second column: Count the number of alphabetical characters in each data cell (use function Len and some subtraction using an array {a,b,c,...} and so on). Have an error condition. So in this column, if you have data A123, you could have the number "1". If you had AB425, you would get the number "2" (because there's an A and a B).
Third column: Print out just the letters (if no letters, then nothing).
Fourth column: Print out just the numbers.
Fifth column: Print out the value of the letters (A = 1, B = 2, AA = 27, AB = 28, and so on).
Now, because I can't figure out how to do a search function for the largest, second largest, third largest, and so on number, I make this even more complicated. You can manually do it, but you can't get the pull-down function to increase the number from 1, to 2, to 3, to 4, when using the function to search for first biggest number, second biggest, so on.
Sixth column: Re-print the data as the numerical value of the letter, then a decimal: "27.85" would represent AA85.
Seventh column: Re-order the data cells in ascending order manually by highlighting them all and clicking sort ascending.
Eigth column: Turn the cells back into their original names (read up to the decimal and turn that into the letters, read up to the decimal using right orientation and print out the number).

Done.

I'm going to figure out how to do this with much fewer steps. I also want to learn better excel functions, since this is a Macgyver solution.

This post was edited by Canadian_Man on Apr 12 2017 09:10pm

The result of this sorting:
#'s come first on list, ordered by the value of their first digit, then second digit, and so on
letter + # combinations come second, ordered by the value of the letter(s), then numbers, and so on

Put data in column B, start at B4.

Column D, starting at D4 =VALUE(CONCATENATE(IF(LEFT(B4, MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789")-1))<>"",COLUMN(INDIRECT(LEFT(B4, MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789")-1))&1)),""), ".", RIGHT(B4, LEN(B4)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789")-1))))

Column E, starting at E4 =UPPER(INDEX($B$4:B999, MATCH(SMALL($D$4:$D$999,ROW()-3), D$4:D$999, 0), 1))

Data Set:
A135
dgs31
ui2
a1
a93
a413
3
AZ
t32
ti89
31516
4214
re35
re99
re000
waq1
aa4
ZZ1
A12
A13
A14
A15
A16
a16

Column D Output
1.135
2905.31
555.2
1.1
1.93
1.413
0.3
52
20.32
529.89
0.31516
0.4214
473.35
473.99
473
15591.1
27.4
702.1
1.12
1.13
1.14
1.15
1.16
1.16

Column E Output
3
31516
4214
A1
A12
A13
A135
A14
A15
A16
A16
A413
A93
T32
AA4
AZ
RE000
RE35
RE99
TI89
UI2
ZZ1
DGS31
WAQ1

This post was edited by Canadian_Man on Apr 13 2017 02:09am

Blah google version butchered it but the stuff I posted above works in excel.