3

I have a column with values like these

A1
A-3 // reads: A (minus 3)
J24
J-2
A24
...

Now I want to sort them first by the leading letter. Then by the following number. But Excel reads the minus as dash and sorts it like that

A1
A2
A3
..
A-1

There are special occasions with trailing letters

F2B
F-2B

What I want my A-Z sorting to look like

A-3
A-2
A-1
A0
A1
..
Z-3
Z-2
Z-1
Z0
Z1
Z2
..

Is there a way to solve this without VBA?

3
  • 1
    Is it always one letter then a positive or negative number then zero or one letter ? Mar 7, 2019 at 13:04
  • 1
    Care to comment on where the data with trailing letters should be sorted?
    – Alex M
    Mar 8, 2019 at 1:02
  • Could you post the real order since I've almost solve it but I just wanna to compare with your original list. Mar 8, 2019 at 7:53

3 Answers 3

2

Example operation

You can split your value x into three parts:

  • the prefix part =LEFT(x)
  • the others =RIGHT(x, LEN(x)-1), which is referred as y below, and contains
    • the number part =IFERROR(VALUE(y), VALUE(LEFT(y, LEN(y)-1))) and
    • the suffix part =RIGHT(y, LEN(y)-LEN(number_part)))

and then just sort them together.

4
  • You have almost solve it,,, but one step behind to get the correct sequence. Mar 8, 2019 at 10:43
  • @RajeshS Oh sorry I didn't see that part. Answer updated to consider the suffix.
    – Arnie97
    Mar 8, 2019 at 11:07
  • ,, no Previous two Helper Col Prefix and Number are enough to Sort, but you have not shown the the Order & Sequence to Sort the Column to get Result . Mar 8, 2019 at 11:14
  • @RajeshS No, the built-in sort method will put for example -3, -2B and 1B in a wrong order.
    – Arnie97
    Mar 8, 2019 at 11:21
0

Sample Excel Implementation Based on Algorithm Below

You can break your strings apart to individual components and custom sort them, but without the column format specifications, only a guess can be provided.

First, setup your spreadsheet like below (column names aren't needed, but were added to line up with the sort dialog). Note that this example assumes your string can have up to 6 characters.

Spreadsheet Layout to Sort Columns

Copy the formulas as follows:

  1. =MID(A2, 1, 1) into column B2.
  2. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 3, 1) = "", "", "-" & MID(A2, 3, 1)), MID(A2, 2, 1)) into column C2.
  3. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 4, 1) = "", "", "-" & MID(A2, 4, 1)), MID(A2, 3, 1)) into column D2.
  4. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 5, 1) = "", "", "-" & MID(A2, 5, 1)), MID(A2, 4, 1)) into column E2.
  5. =IF(MID(A2, 2, 1) = "-", IF(MID(A2, 6, 1) = "", "", "-" & MID(A2, 6, 1)), MID(A2, 5, 1)) into column F2.
  6. Select columns B2 through F2 and copy the formulas down to the last row. Your result should look similar to the screen shot above.
  7. Select columns A1 to F10, right-click, select Sort, and then Custom Sort.... This popup will appear.

Sort Dialog

  1. I used a custom list to specify my sort order which is shown below. Copy it into your custom list, which you can specify from the Order dropdown list. Now sort the columns as in the image and select OK to sort. You may be prompted with a Sort Warning, and I chose "Sort numbers and numbers stored as text separately" (not sure if it matters).

    -9,-8,-7,-6,-5,-4,-3,-2,-1,-0,-Z,-Y,-X,-W,-V,-U,-T,-S,-R,-Q,-P,-O,-N,-M,-L,-K,-J,-I,-H,-G,-F,-E,-D,-C,-B,-A,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9

  2. Here is my end result. Some observations:

    • Minus values come before non-minus values.
    • For minus values, larger numbers come before smaller numbers (e.g. J-24 and J-2)
    • For non-minus values, larger numbers come after smaller numbers (e.g. A1 and A24).
    • Note that the sort list customizations in the algorithm below can be customized to re-order the values in any order as needed.

Final Sorted Results

Explanation of Excel Functions

To separate the first character, I used the formula

=MID(A2, 1, 1)

To extract subsequent characters, I used the formula:

=IF(MID(A2, 2, 1) = "-", IF(MID(A2, N, 1) = "", "", "-" & MID(A2, N, 1)), MID(A2, N-1, 1))`

Where character `N` applies to characters 3, 4, 5, and 6.  The `Else` part of the first `IF` function will take care of character 2 for a non-minus sort. 

In pseudocode, the formula does this:

If character 2 is a minus then prep our current character N for sorting
  If character N is blank then
    Return a blank because we have no character to sort by
  Else
    Return a "-" prepended to character N
    (e.g. `-5`, `-B`, which is understood by custom sort list)
  End If
Else character 2 is not a minus
  Return character N-1 for sorting
  (-1 because minus doesn't exist for non-minus values)
End If

Algorithm with Assumptions

I used a combination of the MID and IF functions to break the string into parts and then applied a custom sort to achieve the end result. I have taken some liberties by making the following assumptions:

  1. The first column is treated separately from the remainder of the string and is always sorted first in ascending alpha order (e.g. A, B, Y, Z).
  2. For columns 2 to the end, values with a minus - are sorted to come before values without one (e.g. -24B, -24A, -2B, -2A, -1, 1, 2A, 2B, 24A, 24B, in that order). Additionally:
    • For values with a preceding minus -, sort order is descending numeric and descending alpha, but with numeric coming before alpha. Examples:
      • Descending numeric: -24 comes before -2, so -24A comes before -2A.
      • Descending alpha: B comes before A with B and A treated like -B and -A, respectively, so -2B comes before -2A.
      • Numeric before alpha: 4 comes before B, so -24B comes before -2B.
    • For values without a preceding minus, sort order is ascending numeric and ascending alpha, but with alpha coming before numeric. Examples:
      • Ascending numeric: 2 comes before 24, so 2A comes before 24A.
      • Ascending alpha: A comes before B, so 2A comes before 2B.
      • Alpha before numeric: A comes before 4, so 2A comes before 24A.

The rules can vary widely depending on how your column is formatted.

Custom Sort List

The sorting is outlined in the custom sort list in the order shown below. Negative numeric and alpha characters are listed descending with numeric coming before alpha. Positive numeric and alpha characters are listed ascending with alpha coming before numeric.

-9,-8,-7,-6,-5,-4,-3,-2,-1,-0,-Z,-Y,-X,-W,-V,-U,-T,-S,-R,-Q,-P,-O,-N,-M,-L,-K,-J,-I,-H,-G,-F,-E,-D,-C,-B,-A,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,0,1,2,3,4,5,6,7,8,9

Note that the sort list can be ordered in any way desired to achieve the preferred results. Here are some examples.

  • Re-order -Z to -A as -A to -Z if you want ascending minus alpha order (e.g. A-2A comes before A-2B).
  • Move -9 to -0 after -Z to -A to get minus alpha before numeric (e.g. J-2F comes before J-24).
  • Move 0 to 9 before A to Z to get non-minus numeric before alpha (e.g. F21 comes before F2A).

This custom sort list is applied to each character individually starting from the left column to the right column to achieve the final sorted result.

4
  • Cool answer. Welcome to Super User. I took care of the images for you.
    – Alex M
    Mar 8, 2019 at 1:03
  • This does not give the correct result for the sample data in the question.  I don’t know whether to congratulate you for constructing a data set for which your solution works, but the answer is wrong. Mar 8, 2019 at 2:18
  • @Scott I think you're assuming facts not in evidence. To the extent that OP defined the desired sort, this answer suits it, as far as I can see.
    – Alex M
    Mar 8, 2019 at 18:10
  • @Scott. Good catch. I overlooked the part where the numbers were descending in the minus sort. Unfortunately, the OP hasn't provided the column format specification, so I had to make some assumptions. The algorithm ended up being more complex, but I think it's flexible enough to achieve the desired sort order. Mar 8, 2019 at 19:38
0

Your issue can be solved using few Helper Columns:

enter image description here

How it works:

  • Unsorted Data Range is B2:B13.
  • Formula in C2. =Left(B2,1), fill it down.
  • Formula in D2. =VALUE(RIGHT(B2,LEN(B2)-1)),fill it down.
  • Select Data to Sort.
  • From HOME Tab click Sort Icon and select Custom Sort.
  • Set Column, Sort ON & Order as shown in Screen Shot, finish with Ok.
  • Finally from Sort Warning Dialogue select Second Option & hit Ok to finish.

N.B.

  • I've included original order of unsorted data in Column A (in Red Color) to Compare with Sorted Data.
  • You may hide both Helper Columns after Data been Sorted.
  • Adjust cell references in Formula as needed.

You must log in to answer this question.