How do I sort a values with letters and numbers?

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

3 Answers

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

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.

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.

8. 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

9. 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.

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

Your issue can be solved using few Helper Columns:

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.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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