Forum Discussion
Custom Sort Alphabet Alphabetizing
So I want to alphabetize a list with a custom list of symbols. I've tried the custom list function but this only sorts cells if the exact word/symbols are in that custom list.
For example, if I had the list,
1. microsoft
2. fl4rs
3. games
4. 7mpe
5. apples
6. notes
7. google
I would want to alphabetize the words based on my own list/"alphabet" (let's say s,e,p,g,4,o,m,a,7,&,i,n,r,c,l,f, etc.). So then it would "alphabetize" the words using that "alphabet" or list of symbols, so it would come out as,
1. games
2. google
3. microsoft
4. apples
5. 7mpe
6. notes
7. fl4rs
Is it possible to do this in Excel? Alphabetize based off your own order of the alphabet or whatever list of symbols.
8 Replies
jonahw218 Inspired by the work of Ghazanfar Abidi, and just for the fun of it I amended my original file and made it "fully dynamic". Make sure that the order of the "key" to your alphabet is in accordance with the sorting order that Excel is using (i.e. numbers first, then symbols, then letters).
jonahw218 I was thinking something similar to what Riny_van_Eekelen laid out in his answer. But instead of encoding characters to numbers, I'm going to alphanumeric so that you can handle more than 10 characters. Sample attached.
Ghazanfar Abidi Well done. And a much neater solution. Don't know what I was thinking.
jonahw218 My suggestion would be that you create a sorting key for every word, where each character is replaced by the "rank" it has in your customised alphabet. Obviously, this needs some extra columns as demonstrated in the attached workbook. I've used the new dynamic array (DA) functionality, TEXTJOIN and SORTBY in the example. Add a word to the text list or characters to your "alphabet" and the formulae ranges should automatically expand. See if it works for you. If your excel doesn't recognise DA or the TEXTJOIN and SORTBY functions, it can also be done without it, but it requires more copying and pasting.
- Could you explain what each of these functions are doing individually and as a whole in these equations and what the differences between them are if you're up to it 🙂 Thanks!
=IFERROR(MATCH(MID(Table1[Text],COLUMN()-COLUMN($C$1),1),Table2[My Alphabet],0),0)
&
=IFERROR(MATCH(MID($A2,COLUMN()-COLUMN($C$1),1),Table2[My Alphabet],0),0)jonahw218 The first formula uses the newest dynamic array functionality in Excel. The second should work in all versions. What it does is look at your text, and assign a "key character", based on your alphabet, to each of the characters in your text. Not easy to explain, though, in words.. In my original example a P would translate to 3 and an E to 2 etc. So "PE" becomes "32". "PS" would become "31" and thus come before PE when you sort it "your way".
In my latest file I improve the fomulae, inspired by Ghazanfar Abidi. They, basically, achieve the same. You can expand the "My Alphabet to how many characters you want, as long as you make sure that the "Key" is in the correct order, so that Excel will sort everything the correct way.
If this doesn't answer your question, I can give it another try. Let me know!
Riny_van_Eekelen and @Ghazanfar Abidi, thanks for all your help! I'll have to play with it to try and figure out how to get it to work. In all of the TextSorted lists for all of the documents you all sent, it displays "#NAME?" for all of them. Does that have to do with that maybe my version of Excel doesn't recognize the SORTBY function?
Also, with the one that converts the symbols into numbers, I found that when it CONCATs the numbers, I can make it add dashes in between to distinguish the double digit numbers. I also put in my own "alphabet" which includes numbers, but whatever the number from the "text" list is, the individual cell numbers convert the text numbers into zeros, so I'm not sure why it's treating the numbers differently from the letters. The reason why I don't think the alphabetizing way will work is because I have 61 symbols in my "alphabet" while the alphabetizing way only allows for 47.
