How is alphabetization determined in Excel and Google Sheets?

Question

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?

score 1 · Accepted Answer · answered Jan 08 '23 at 21:41

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).