Namaste!
How to sort a list alphabetically if it is in a language other than English (e. g. Nepali/ Hindi)?
Thanks in advance.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
sorting foreign language alphabetically
0
Post Edited
Title: Title was not descriptive.
Discussion
My answer assumes you have already tried, to no avail, the regular sorting methods in Excel.
don (rep: 1989) Jul 28, '17 at 4:40 pm
@don,
I'm grateful for your reply but I "don't have permission to vote yet". Don't know why.
I'm grateful for your reply but I "don't have permission to vote yet". Don't know why.
Chhabi Acharya (rep: 111) Aug 11, '17 at 6:41 am
Answers
0
Selected Answer
This might take some tinkering...
I found this macro online:
Public Function AlphaSortHelper(s As String)
Dim alpha As String, tmp As String, c As Long
alpha = "AaBbCcDd"
tmp = Chr(183)
For c = 1 To Len(s)
If CBool(InStr(1, alpha, Mid(s, c, 1))) Then
tmp = tmp & Format(InStr(1, alpha, Mid(s, c, 1)), "000")
Else
tmp = tmp & Format(AscW(Mid(s, c, 1)), "000")
End If
Next c
AlphaSortHelper= tmp
End FunctionThis is a UDF. You are supposed to put this formula into the cell to the right of the column that you want to sort and copy it down.
If you want to sort column A, put this in column B and copy down:
AlphaSortHelper(A1)
Then you can use this column to sort the list.
Important: You must update the macro with your alphabet in the order in which you want to sort it. The code has the start of the English alphabet to illustrate how to do it.
alpha = "AaBbCcDd"
Replace AaBbCcDd with your alphabet.
This might take some effort to get it working with your language but let me know how it goes!
Discussion
@Don Your little tool can't do the job. The question is how to tweak it. Nepali script, or Hindi, is using Unicode characters, 64000 of them, half represented by negative numbers. The String 'Alpha' would have to be created in a formidable function, quite unlike the code you found. Then the method of creating the 'tmp' string should be changed as well as, of course, the format of each number since Unicode character codes have five digits to ASCII's three. I suppose the problem of negative numbers could be overcome by just adding 32768 to each AscW() value.
Variatus (rep: 4889) Jul 28, '17 at 11:11 pm
I found this script being used for another language, one from Eastern Europe I think, and figured I'd throw it his way. But, I gotta say, you sure know a lot more about this than I do! I'm a little embarrassed by answer now haha.
don (rep: 1989) Jul 29, '17 at 3:19 am
After some more thought, I would be willing to try to adjust @Don's find to Unicode requirements. Chhabi Acharya would have to supply the sorting sequence(s). In fact, I would prefer more than one, say Hindi and Nepali so that my code could deal with either one. A "sorting sequence" is a string of characters or signs, in the target language, sorted in the sequence in which sorting should be done. In English that would be "ABCDE..." to Z, or "AaBbCc..." to Zz if caps are to be sorted before lower case, or "0123456789AaBbCc ..." if numbers are to be sorted before characters. Actually, Don's code does that automatically, but if numbers in the target language aren't Western Arabic numbers they would have to be included in the sorting sequence.
Variatus (rep: 4889) Jul 29, '17 at 9:17 pm
@Variatus,
Thank you very much!
Thank you very much!
Chhabi Acharya (rep: 111) Aug 11, '17 at 6:43 am
0
Each script (not language) has its own sorting order. However sorting orders may also be different for languages using the same script, for example, where they have special characters. Windows determines the sorting order to use by the "locale" you have set for your computer. Got to Control Panel > Language and Region > Region > Formats => Format.
If you need to sort script/language of a locale other than the one set for your computer things become rather complicated. This link offers a preview of what you may have to study.
I tried this once and gave it up. It isn't too hard in VB or C#, but VBA just isn't intended for this sort of programming.
Discussion
@Variatus,
I'm grateful for your suggestion but I "don't have permission to vote yet". Don't know why.
I'm grateful for your suggestion but I "don't have permission to vote yet". Don't know why.
Chhabi Acharya (rep: 111) Aug 11, '17 at 6:40 am
0
Namaste!
Thanks for your genuine replies. I think I've got a solution, though not good.
Now, I copy the list and paste on websites that convert "Preeti" font to unicode. Next job is to copy the result and paste in Excel. The unicodified list can be sorted alphabetically.
Edited:
One of the sites through google search is of Ashesh.
Thanks!
Discussion
Yes, I think that is the better solution. You might add the link to the website here where you get the conversion done for others who might have a similar problem. Put in an "Answer", not a "Discussion".
Variatus (rep: 4889) Aug 10, '17 at 10:17 pm
Thank you very much for your feedback.
Chhabi Acharya (rep: 111) Aug 11, '17 at 6:37 am