1
I have a column in Excel that's a letter followed by a number. When I sort, it considers each digit. For example, if I have a column in Excel like this:
A1
A10
A11
A2
B1
B2
B19
B21
A3
it will get sorted to this:
A1
A10
A11
A2
A3
B1
B19
B2
B21
Is there a way to have it sorted by the letter and then by the entire number? For example, in my example above, A10 appears before A2 (even though 2 is smaller than 10); I'd like a way to have A10 appear after A2 based on the fact that 2 < 10, so my previous example should be sorted like this:
A1
A2
A3
A10
A11
B1
B2
B19
B21
Is there a way to do this in Excel? If so, how?
I suppose that splitting these into two columns would enable me to do this, but is there a way to accomplish this without splitting them?
EJoshuaS - Reinstate Monica
Posted 2017-10-06T19:09:36.077
Reputation: 407
2You will need to parse the values and either create two columns one with the number and the other the letter, or change all numbers to the same number of digits; for example:
A01,A02,A10,... All numbers will need to have the same number of digits. – Scott Craner – 2017-10-06T19:18:11.330@ScottCraner Both of those worked for me - thanks. If you add as an answer I'd be glad to accept it. – EJoshuaS - Reinstate Monica – 2017-10-06T19:28:22.470
1what you have is actually a string. You will have to choose one of the two options given by @ScottCraner. the Lexicographical order in Excel will sort your data the way you don't want you. – Thales – 2017-10-07T18:30:11.007