Let's walk through this solution to see how it works. First, we define some groups of characters. The characters that are in spChar list are characters like "!" and "_". They will be sorted in the order they appear in the list. This does not include all the special characters, because I do not know what order they should have. The characters in the numChar list are numeric characters. They will be sorted numerically instead of in dictionary order. The last group is alphaChar, the alphabetic characters. They will be sorted in the alphabetic order, ignoring case.
spChar = {" ", "!", "_"};
numChar = Characters["0123456789"];
alphaChar = Join[CharacterRange["A", "Z"],
CharacterRange["a", "z"]];
Now we define a function that classifies the characters in a string. The function returns a list of integers: 1 for a special character, 2 for a digit, 3 for an alphabetic character, and zero for unknown characters.
classify::unknownchar =
"Unknown character `1`. Please add to spChar list in the correct order.";
classify[c_String /; StringLength[c] == 1] :=
Which[
MemberQ[spChar, c], 1,
MemberQ[numChar, c], 2,
MemberQ[alphaChar, c], 3,
True, Message[classify::unknownchar, c]; 1]
classify[str_String] := Map[classify, Characters@str]
Now define functions to compare strings composed of characters all of the same type. If the characters are special, the comparison is based on the order given in the spChar list. If the characters are numeric, convert the strings to numbers and compare numerically. If the characters are alphabetic, convert to upper case and compare. If the strings are identical, return 0. If the strings are in the proper order, return -1 and if the strings need to be swapped, return 1.
cmpSpecial[str1_String, str2_String] := Block[
{c1, c2, cmp = 0, p1, p2, s1 = str1, s2 = str2},
While[cmp == 0 && StringLength[s1] > 0 && StringLength[s2] > 0,
c1 = StringTake[s1, 1]; c2 = StringTake[s2, 1];
p1 = First@Flatten@Position[spChar, c1];
p2 = First@Flatten@Position[spChar, c2];
cmp = Sign[p1 - p2];
s1 = StringDrop[s1, 1]; s2 = StringDrop[s2, 1];
];
If[cmp == 0 , cmp = StringLength[s1] - StringLength[s2]];
cmp
]
cmpNumeric[str1_String, str2_String] := Block[{cmp},
cmp = ToExpression[str1 <> "-" <> str2];
If[cmp == 0, cmp = StringLength[str2] - StringLength[str1]];
Sign[cmp]
]
cmpAlpha[str1_String, str2_String] := Block[
{s1 = ToUpperCase[str1], s2 = ToUpperCase[str2]},
Which[str1 == str2, 0,
s1 == s2 , If[OrderedQ[{str1, str2}], -1, 1],
True, If[OrderedQ[{s1, s2}], -1, 1]
]
]
Now define a function that examines two strings and compares them according to their substrings. The numbers get sorted behind the special characters and in front of the alphabetic characters.
compare::badtype = "Invalid type value `1`.";
compare[str1_String, str2_String] := Block[{
class1 = SplitBy@classify[str1], class2 = SplitBy@classify[str2],
cmp = 0, f1, f2, n1, n2, s1 = str1, s2 = str2, type1, type2},
While[cmp == 0 && Length[class1] > 0 && Length[class2] > 0,
type1 = First@Flatten[First@class1];
type2 = First@Flatten[First@class2];
cmp = Sign[type1 - type2];
If[cmp == 0,
n1 = Length[First@class1]; n2 = Length[First@class2];
f1 = StringTake[s1, n1]; f2 = StringTake[s2, n2];
cmp = Switch[type1,
1, cmp = cmpSpecial[f1, f2],
2, cmp = cmpNumeric[f1, f2],
3, cmp = cmpAlpha[f1, f2],
_, Message[compare::badtype, type1]; 0
];
If[cmp == 0, class1 = Drop[class1, 1]; class2 = Drop[class2, 1];
s1 = StringDrop[s1, n1]; s2 = StringDrop[s2, n2]]
]
];
If[cmp == 0, cmp = Sign[Length[class1] - Length[class2]]];
Not[cmp == 1]
]
Now define a function ssSort that uses the above comparison function to sort a single Excel spreadsheet. This function takes 3 arguments: the spreadsheet data array, the index of the column to sort on and the number of rows to skip for headings. If there are headings, the last argument can be omitted. If there are no headings and we want to sort on the first column, then only the first argument is required.
ssSort[
data_List /; ArrayDepth[data] == 2,
nColumn_Integer /; nColumn > 0,
nSkip_Integer /; nSkip >= 0] :=
If[nSkip == 0, Sort[data,
compare[Part[#1, nColumn], Part[#2, nColumn]] &],
Join[data[[;; nSkip]],
Sort[data[[nSkip + 1 ;;]],
compare[Part[#1, nColumn], Part[#2, nColumn]] &]]]
ssSort[
data_List /; ArrayDepth[data] == 2,
nColumn_Integer /; nColumn > 0] := ssSort[data, nColumn, 0]
ssSort[data_List /; ArrayDepth[data] == 2] := ssSort[data,1,0]
We are all set and ready to test it. We read the first spreadsheet of an Excel workbook like this: ss = First@Import["tmp.xls"]; But let's use the following test data instead. We can always export this data to an Excel workbook to make a more complete test.
ss = {
{"Heading 1", "Heading 2", "Heading 3"},
{"Col 1 row 1", "Ie4 01", "Col 3 row 1"},
{"Col 1 Row 7", "Ie4_128", "Col 3 Row 2"},
{"Col 1 row 3 ", "Ie5", "Col 3 row 3 "},
{"Col 1 row 4", "Ie4!01", "Col 3 row 4"},
{"Col 1 row 5 ", "Ie501sp2", "Col 3 row 5 "},
{"Col 1 row 6 ", "abC", "Col 3 row 6 "},
{"Col 1 row 2 ", "Ie6","Col 3 row 7 "},
{"Col 1 row 8 ", "abcde","Col 3 row 8 "},
{"Col 1 row 9 ", "Ie401sp2", "Col 3 row 9 "},
{"Col 1 row 10 ", "Ie4_01","Col 3 row 10 "},
{"Col 1 row 11 ", "abc", "Col 3 row 11 "}};
Let's display the unsorted data, then set the sort index to 2 and the number of heading rows to 1, then display the sorted data:
ss//TableForm
ndxSortColumn = 2;
nSkipRows = 1;
ssSort[ss, ndxSortColumn, nSkipRows] // TableForm
So, that's it. The spreadsheet rows are sorted in the "natural order" given in the original question. However, there are other special characters that still need to be added to spChar list in this code.
One final note about reading Excel spreadsheets. We can read the workbook like this wkb=Import["tmp.xls"];ArrayDepth[wkb]. This should return 3 which means there are one or more spreadsheets in the workbook. We can take the second spreadsheet like this ss2=wkb[[2]]; ArrayDepth[ss2]. This should return 2, meaning the spreadsheet has both rows and columns. This is important, because if we try pass an entire workbook to ssSort instead of just a spreadsheet, ssSort should return unevaluated.
