2

I was recently sorting data in Google Sheets that involved the word "hello" in various languages. An excerpt of this sheet is below. Column C was calculated using the =UNICODE(LEFT(B2,1)) function transposed down the C column. The top-left of the table is cell A1.

Language Hello Unicode Value of First Character
English hello 104
Armenian Բարեւ Ձեզ 1,330
Amharic ሰላም 4,656
Bengali হ্যালো 2,489
Korean 여보세요 50,668
Japanese こんにちは 12,371
Chinese 你好 20,320

I then sorted the sheet from A to Z, using the Sort sheet by Column B (A to Z) menu option in the Data tab. However, the result was the table above. This wasn't what I was expecting, because the first character of cell B6 (여) has a Unicode hex value of U+C5EC, which is greater than the first character of cell B7 (こ, U+3053). Excel Online's attempt to sort the same table from A to Z was the following:

Language Hello Unicode Value of First Character
English hello 104
Armenian Բարեւ Ձեզ 1,330
Japanese こんにちは 12,371
Bengali হ্যালো 2,489
Amharic ሰላም 4,656
Korean 여보세요 50,668
Chinese 你好 20,320

I previously believed that Google Sheets' and Excel's alphabetical ordering involved the Unicode codepoint of each character, but this appears to be incorrect. Here are other related notable findings:

  • Characters that look similar appear to be grouped together, such as both £ U+00A3 POUND SIGN and ¥ U+00A5 YEN SIGN coming before A U+0041 LATIN CAPITAL LETTER A.
  • Some CJK characters, like ㈮ U+322E PARENTHESIZED IDEOGRAPH METAL, are alphabetically before before almost every other character, including most Latin characters.
  • Many look-alike characters are alphabetically adjacent to their Basic Latin counterparts, like Å U+212B ANGSTROM SIGN and A U+0041 LATIN CAPITAL LETTER A both being before B, but not all look-alikes, such as Α U+0391 GREEK CAPITAL LETTER ALPHA coming after Z.

A link with all of these aforementioned Google Sheets tests is available here.

I personally can't find much reason to this ordering. How do Google Sheets and Excel alphabetize text?

1 Answer 1

1

Microsft Windows Sorting and String Comparison

Peeling back the first layers exposes the incredible complexity of sorting strings linguistically. You may have more success attacking this problem from the language-locale side combined with your own language options. The complexity of sorting strings in just one language with multiple different valid orderings is bad enough but dwarfed by your use case with multiple languages and merged sorting.

This is because "string sorting and comparison are language-specific. Even within languages based on the Latin script, there are different composition and sorting rules." Source

The table that follows is a Latin script example from Microsoft that hints at the scope of the challenge. The table that immediately follows it is simply the same table with the exceptions and their positions relative to English-US enumerated. Perfect matches were omitted to reduce the visual noise.

Expected Order by Language-Locale

English-US French-France Spanish-Spain
cote cote cote
coté côte coté
côte coté côte
côté côté côté
Namibia Namibia Namibia
ñandú ñandú número
ñú ñú ñandú
número número ñú

Differences Above Relative to English-US

English-US French-France Spanish-Spain
1 - -
2 3 -
3 2 -
4 - -
5 - -
6 - 8
7 - 6
8 - 7

Resource

Microsoft has an expansive article Sorting and string comparison that is a great place to start reading.

Some excerpts:

Each language has a unique set of rules (sometimes more than one set of rules) for how language strings should be "sorted" or "collated" into an ordered list. User expectations for those lists may vary based on where the information is presented, such as spreadsheets, tables, dictionaries, and telephone books. And sometimes, such as for the file system or a web URL, machine consistency is more important than rules for any specific human language.

In Swedish, for example, some vowels with an accent sort after "Z," whereas in other European countries the same accented vowel comes right after the non-diacritic vowel. In Hawaiian, all the vowels come first. Languages that include characters outside the Latin script have special sorting rules.

Asian languages have several different sort orders depending on phonetics, radical order, number of pen strokes, and so on. Phonetic order can depend on context, such as a phone directory or a dictionary.

One important case where code frequently runs into problems is supporting users who use the Turkish alphabet. For these languages, there are four variation of the character i and they don’t map to the same casings as English or other languages. This is called the Turkish-I problem.

Because each language sorts uniquely and sorting rules tend to be complex, implementing sorting correctly is difficult.

In Closing

If the string sort order across languages and locales matters to you programmatically, you should limit it to what is absolutely necessary given the inherent challenges of implementing anything that diverges from the platform's rules (Windows).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.