Forum Discussion
Custom Sort Alphabet Alphabetizing
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.
