-1

I have codes which includes numbers and letters (example "1A1", "3B2" and "10C3") and I have problem with sorting those. Excel will automatically sort "10C3" before "1A1" but I need to sort the codes by the first value (which can be 1, 2 or 3 characters long) then by the letter and the last number.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Jehhi
  • 11
  • 3
  • 3
    I think you will have to handle this from VBA, because you would need regex capabilities to isolate the leading number, to use that for sorting (followed by the rest of the string). – Tim Biegeleisen Aug 23 '18 at 09:50
  • 1
    You will need to write your own sort algorithm or split it into 3 columns like `1 | A | 1` and use them for sort. See [Learning Regular Expressions](https://stackoverflow.com/questions/4736/learning-regular-expressions) for how you can split these into 3 columns with a RegEx. Try something on your own first and if you get stuck or errors come back with your code. – Pᴇʜ Aug 23 '18 at 09:52
  • @Pᴇʜ I agree, and I actually vote for storing the numeric and text data separately. – Tim Biegeleisen Aug 23 '18 at 09:52
  • 1
    Also, when updating post please include some sample data in the correct layout. For regex solutions that pattern for use needs to be clearly defined e.g. Are the codes the only thing in a cell and only occur once per cell? – QHarr Aug 23 '18 at 10:21

2 Answers2

0

Assume your first code is in cell A2. If you add two helper columns, and split your codes into them as follows:

  • Col B2: =LEFT(A2,LEN(A2)-2)
  • Col C2: =RIGHT(A2,2)

Then sort by B & C.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
johnb25
  • 18
  • 2
  • Actually this works just fine for the purpose, thanks! Also VBA would work but I don't think that there is need for that. – Jehhi Aug 23 '18 at 11:28
0

What you're trying to do can't be done with native sort functionailty. This is because as a human you can see the numbers at the start of that text and separate them visually.

Excel just sees the whole thing as text, and sorts it based on pre-determined rules in in the program's logic.

I have no doubt that some people on here can create mind-boggling formulas that could do this, however this wouldn't serve you well unless you could understand what they are doing at every step.

My suggestion here would be to either split the sections out manually and sort, or to make an attempt using some VBA - if you then get stuck with the VBA you can always come back and ask :)

SierraOscar
  • 17,507
  • 6
  • 40
  • 68