Please HELP! Custom Sort List

One way I can think of would be add a helper column. This formula assumes column B contains the Letter/Number

=IF(ISNUMBER(B1),CODE(B1)+122,IF(ISTEXT(B1),CODE(B1),""))

And sort by the helper column

Unless the word Text is also in each cell?

This might do the trick...

Add this in a new column and sort by it

=IFERROR(CODE(TRIM(MID(B1,FIND(" ",B1),LEN(B1)-(FIND(" ",B1)-1)))+0)+122,CODE(TEXT(TRIM(MID(B1,FIND(" ",B1),LEN(B1)-(FIND(" ",B1)-1))),"0")))

Edit: Just realized, this won't work for double-digit or double-letter entries.... 11 will sort as 1, and AB will sort as A

Originally Posted by Jwildcat

I seem to be stumped. It seems you are trying to turn the text into a number which we can sort by number.

Is there another way to rewrite the custom sort list so it will sort the way I want it to?

What my formula did is strip the "Text " part away, to be left only with letters or numbers, then to determine the character code of that letter or number

Where A = 65 by default, B = 66. But 1 = 49, 2 = 50, so if it was a number and not a letter, I am adding 122 (which is the code for lowercase "z") to the characters code value, to make sure letters are always LOWER code values than numbers

Resulting in 1 = 171, 2 = 172, and so on, while
A = 65, B = 66

So they will sort by Letters, then Numbers

Looks like newdoverman also came up with a similar angle of attack, looks like it has the same disadvantage as mine though, multiple digits won't sort


I don't really have any experience with the custom sort lists unfortunately, I'm sure someone will be by though!

How many variations of words are there before the character to sort by?

Or, is it always two words? Is there a special character at the end before the number? (for example, "This is the data: 2")

Enter this in a helper column next to your values and copy down then select all the data (your data and helper column) and Sort and Filter, Custom Sort and sort on the helper column smallest to largest.

Formula:

=IF(ISNUMBER(RIGHT(A2,1)*1),CODE(RIGHT(A2,1))+100,CODE(RIGHT(A2,1)))

	A	B	C
1
2	•Text C	67	=IF(ISNUMBER(RIGHT(A2,1)*1),CODE(RIGHT(A2,1))+100,CODE(RIGHT(A2,1)))
3	•Text D	68
4	•Text M	77
5	•Text 1	149
6	•Text 8	156

You could use this part of my formula though, to strip the numbers or letters out of the cell into a helper column, and try and sort by it?

=TRIM(MID(B1,FIND(" ",B1),LEN(B1)-(FIND(" ",B1)-1)))

This should take care of the number problem: If there is a possibility of lower case letters then change the 100 to 200.

	A	B	C
1
2	•Text A	65	=IF(ISNUMBER(RIGHT(A2,1)*1),MID(A2,SEARCH(" ",A2),5)*1+100,CODE(RIGHT(A2,1)))
3	•Text C	67
4	•Text D	68
5	•Text M	77
6	•Text 1	101
7	•Text 152	252
8	•Text 810	910
9	•Text 8001	8101

This seems to work with the example given but I hope someone can shorten this "beast". Enter this in a helper column beside the data and copy down. Select the original data AND the helper column and sort on the helper column.

Formula:

=IF(ISNUMBER(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," 12/LTR","",1)," 1/LTR","")," 6/750ML","")," 6/1LTR","")," 6/LTR",""),1)*1),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," 12/LTR","",1)," 1/LTR","")," 6/750ML","")," 6/1LTR","")," 6/LTR",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," 12/LTR","",1)," 1/LTR","")," 6/750ML","")," 6/1LTR","")," 6/LTR",""))-2)   &"ZZZ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," 12/LTR","",1)," 1/LTR","")," 6/750ML","")," 6/1LTR","")," 6/LTR",""))

scrape this idea...becoming too behemoth of a formula
look at next post

actually enhancing newdovers solution

Formula:

=SUBSTITUTE(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),9)),"Z"),"80","ZZZ")

VODKA HANGER ONE 12/LTR becomes VODKA HANGER ONE Z...bring it down the bottom