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?

3
  • 2
    You 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 Oct 6 '17 at 19:18
  • @ScottCraner Both of those worked for me - thanks. If you add as an answer I'd be glad to accept it. – EJoshuaS - Reinstate Monica Oct 6 '17 at 19:28
  • 1
    what 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 Oct 7 '17 at 18:30
1

(Can I add it as an answer?)

Parse the values, creating a column of letters and a column of numbers. Alternatively, you can make sure each value has the same number of digits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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