0

I am sorting in MS Excel 2016. DATA:

***Column***  A
***Header*** Room
***(data)*** A16
             A116
             A21
             A15

Sort: Sort Warning. I chose "Continue with the current selection" (not Expand the selection.") as this is just a test. Sort by Column A Sort on Values Order A to Z

***Output:***
A116
A15
A16
A21

My question is why did A116 appear first and not last?

3 Answers 3

1

The A prefix makes those values text rather than numbers, so it is sorted from left to right as text. The left to right sort means that, in this case, only the first three characters affect the order since all the entries are sorted before it gets to the 6 in A116. candle comes before cat when sorting text.

1

To expand on fixer1234’s answer: since the data contain letters, Excel does a lexicographical sort (which Excel calls “A to Z” order) rather than a numeric sort.  This means it treats the data as words (i.e., arbitrary strings), even though they contain numbers.  As a classic example, consider the strings A1, A2, A3, …, A97, A98, A99.  After sorting these (I suggest that you try it yourself), A1-A9 will be in the “right” order, as will A10-A99 — but A10-A19 will be placed between A1 and A2, A20-A29 will be placed between A2 and A3, etc.  The analogy that I like to offer is that, alphabetically, words like “HA”, “HE”, “HI” and “HO” sort before “I”.  This illustrates how two-digit numbers can be sorted ahead of one-digit numbers.

0

It's taken the beginning numerals in order. 11 is the "lowest" number.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .