Numbers not sorting names correctly

Question

For some reason I’m having issues sorting column D. When I tap “Sort Ascending” it sorts everything correctly except books 5 and 7:

“12, 13, 14, 15 16 17, 18, 22, 23, 5, 7”

I’ve tried deleting all the “Tintin” sells and then recreating everything with the same issue.

Answer 1

The sort order is "correct":

• Numbers are sorted by value.
• Text is sorted in lexicographic order.

"The Adventures of Tintin #5" is (obviously) not a number, it is text and is therefore ordered lexicographically.

Lexicographic order means comparing the first element, if they are equal, comparing the next element, and so on, until you find the first difference (which determines the sort order) or you run out of elements (in which case the shorter one comes first). So, the text "#5" comes after the text "#23" for the same reason that the text "#e" comes after the text "#bc".

What you want is natural sort order, which sorts text including digits by treating digit sequences as numbers.

For example, in lexicographic order, the text "ab12c3" would be treated as 6 characters:

1. the character "a"
2. the character "b"
3. the character "1"
4. the character "2"
5. the character "c"
6. the character "3"

Whereas in natural order, the same string would be treated as five elements:

1. the character "a"
2. the character "b"
3. the number 12
4. the character "c"
5. the number or character "3" (doesn't really matter for single-digit numbers)

Unfortunately, it seems Numbers does not support natural ordering despite one lone guy on the entire WWW claiming otherwise.

You will have to implement something yourself. You can do one (or more) of the following:

• Split the column into the text and number parts: How to sort column by numbers in Numbers?
• Create a separate sort key in a hidden column.
• Add leading zeroes so that the numbers are always the same length, i.e. have the same number of digits.

Note that numbers aren't the only tricky bit about sorting text. Just some random examples: in Germany, there are two different ways to order Umlauts which are used in different circumstances. So, where to sort my name, depends on context. The Netherlands and Belgium share a language but disagree about how to sort names: one would sort a name like "van Hoorn" under "v" and one would sort it under "H" (but still keep the "van").

Denmark originally used the digraph "Aa" for an /å/-vowel, but switched to "Å" in 1948. However, in limited cases, it is still allowed to write "Aa". "Aa" is sorted the same as "Å", as a single letter at the end of the alphabet. But only if it is also pronounced as an /å/-vowel. In loan words or proper names from other languages, where "Aa" is pronounced as a long /a:/-vowel, it is treated as two separate letters "a" and "a". Which means in a list of place names, the German city of Aachen would be sorted near the top, whereas the Danish city of Aalborg would be sorted near the bottom.

Oh, except that did change at some point, so today Aachen and Aalborg would both sit at the end of the list. Which means, it not only depends on which culture you are sorting in, but which time as well.

Human language is weird.