Custom Alphabetical sort for ancient languages

**wali** · 10-11-2009, 04:19 PM

Hello Every one,

I have to sort wordlists of some ancient and asian Languages in Excel. The problem is that Excel's sort command does not support those languages. Is it possible to make a code which could sort the wordlist according to the list of alphabets in a seperate worksheet? I would like to put the wordlist in worksheet "1" and then put the Alphabets of the language in right order in worksheet "2".

Now there is another Problem that the letters of these languages are not recognized by VBA code. I mean those letters cant be used in VBA code. If one does instead of that letter "?" appears in code. That problem can be solved in puting the specific ChrW number instead of the original letter in VBA code.

For example if i want use a specific Puschtu language Letter "آ" in VBA code then instead of

Please Login or Register  to view this content.

i would have to use the ChrW number of that letter which is 1570. And the code would then look like:

Please Login or Register  to view this content.

Now i have two worksheets. The first worksheet "1" contains the wordlist of an ancient or asian language and the second worksheet "2" contains the alphabets of that language in right order. Column A of "2" contains of the original letters and Column B contains of ChrW numbers of those letters.

I will be very grateful if someone could help me solve my problem.

I am attaching an example file containing Puschtu wordlist.

Thank you very much.

**Leith Ross** · 10-11-2009, 04:42 PM

Hello Wali,

Excel and VBA sorting is based on ASCII and not Unicode. There is probably a way to do the sort and suspect it would involve changing the language ID. It seems reasonable that if you have the Unicode for the alphabet, Excel or VBA should be able to sort it correctly. After all, Excel was developed to be internationally aware. I'll have to research this before I can give you an answer.

**Leith Ross** · 10-12-2009, 01:31 AM

Hello Wali,

What I found out so far is that Unicode or UTF-8 encoded characters aren't necessarily sorted by the order in which the characters appear in the Unicode code table. Each language has its own method in which the characters are allocated (placed in a specific order) and the character encoding doesn't correspond to this layout. It seems that locale ID needs to be loaded for the language before the system knows how the characters are to be allocated when sorting.

**wali** · 10-12-2009, 08:28 AM

Thank you very much for your answer.

The languages i work with are all not supported by the windows and therefore i can't change my system ID to that language. What i can do is to replace the specific letters of those languages to Latin letters or asci letter in such a way that one symbol is not assigned twice, then sort the list through custom order according to my need and then replace all the symbols to the original letters. But that would mean that i at least have to find a way to sort my worksheet in a custom order.

In excel one can order Lists from A-Z or from Z-A as far my limited knowledge of excel is concerned. Is there any way or method for latin letters or symbols through which i can define custom order for the sorting process. For example what if i want to have a custom order C,B,A,D,F,G,Z... instead of A,B,C,... or Z,Y,X,...?

One more thing is that some of the languages i work with are written from right to left and some of them from left to right. Now i would have to mirror those written from right to left before i they can be sorted by a custom order! or not?

Well It will be great help as well if i knew how to sort western languages like english according to my own custom order. If there is a macro for that then that would help me too.