Search code examples
sortinglibreoffice-calcalphanumeric

Sort text column alphanumerically (letters before numbers)

I am trying to sort our inventory list alphanumerically by part number such that letters are sorted before numbers. For instance, given the list:

0004006A
AN42B10
0400975
1968
MS21042L3
0004006
AN414A
J961393
AN4H16A
SR22SCW20
STD1410
4914
15KE51CA
21
560

the sorted list should be:

AN4H16A
AN414A
AN42B10
J961393
MS21042L3
SR22SCW20
STD1410
0004006
0004006A
0400975
15KE51CA
1968
21
4914
560

Currently, I can only get it to sort with numbers before letters so the list looks like:

004006
0004006A
0400975
15KE51CA
1968
21
4914
560
AN414A
AN42B10
AN4H16A
J961393
MS21042L3
SR22SCW20
STD1410

(Note especially AN4H16A coming after AN42B10 and AN414A rather than before.)

I have tried adding a custom list (A, B, C, ..., 7, 8, 9) but get the same result sorting by that list.

Is this possible?

Solution

  • The following solution is for LO Calc. If the data is in A1 through A15, then enter the following formula into B1.

    =IF(LEN($A1)<COLUMN()-1;-1;IF(CODE(MID($A1;COLUMN()-1;1))<=CODE(9);MID($A1;COLUMN()-1;1)+27;CODE(MID($A1;COLUMN()-1;1))-CODE("A")))

    This gets the first character of the string in A1 and then determines a sorting value for that character, with "A" becoming 0 (the first in sorted order) and "9" becoming 36 (the last in sorted order).

    Now, drag and fill over to J15 for the rest of the characters in the string, then down to J15 for the other strings.

    Then, go to Data -> Sort. Sort Key 1 is column B, sort key 2 is column C, and so on through J.

    sort menu

    Alternatively, select A1 through A15 and then run the following Python macro.

    import uno

def custom_sort():
    oSelect = XSCRIPTCONTEXT.getDocument().getCurrentSelection()
    rowTuples = oSelect.getDataArray()
    rowTuples = sorted(rowTuples, key=letters_then_numbers)
    oSelect.setDataArray(rowTuples)

def letters_then_numbers(rowTuple):
    strval = str(rowTuple[0])
    sresult = ""
    for c in strval:
        if c in (str(i) for i in range(10)):  # if character is a number
            c = chr(ord('z') + int(c))        # then order it after z
        sresult += c
    return sresult

g_exportedScripts = custom_sort,