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:
=MID(A2, 1, 1)
into column B2.
=IF(MID(A2, 2, 1) = "-", IF(MID(A2, 3, 1) = "", "", "-" & MID(A2, 3, 1)), MID(A2, 2, 1))
into column C2.
=IF(MID(A2, 2, 1) = "-", IF(MID(A2, 4, 1) = "", "", "-" & MID(A2, 4, 1)), MID(A2, 3, 1))
into column D2.
=IF(MID(A2, 2, 1) = "-", IF(MID(A2, 5, 1) = "", "", "-" & MID(A2, 5, 1)), MID(A2, 4, 1))
into column E2.
=IF(MID(A2, 2, 1) = "-", IF(MID(A2, 6, 1) = "", "", "-" & MID(A2, 6, 1)), MID(A2, 5, 1))
into column F2.
- Select columns B2 through F2 and copy the formulas down to the last row. Your result should look similar to the screen shot above.
- Select columns A1 to F10, right-click, select Sort, and then Custom Sort.... This popup will appear.
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
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:
- 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
).
- 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.
1Is it always one letter then a positive or negative number then zero or one letter ? – Stormweaker – 2019-03-07T13:04:56.690
1Care to comment on where the data with trailing letters should be sorted? – Alex M – 2019-03-08T01:02:20.623
Could you post the real order since I've almost solve it but I just wanna to compare with your original list. – Rajesh S – 2019-03-08T07:53:30.503