Sort alphabets and numbers first before the minus sign in LibreOffice Calc

In Excel 2010, any alphabet or number is sorted first before the minus sign -. But in LibreOffice Calc, the minus sign is sorted first before any alphabet or number. I have this Excel file that I want to continue editing in Calc, but LibreOffice messes the sorting whenever it encounters a minus sign in the column. The following is the Excel file sorting, which is what I would want to achieve:

Bitan-agan
Bit-os

while in Calc, the sorting outcome is the following:

Bit-os
Bitan-agan

I cannot find any options to allow the minus sign to be sorted after alphabets and numbers. How can I retain the Excel file sorting when sorting using Calc?

1 Answer

To me, the way Calc does it seems like normal alphabetical order for roman script - spaces and punctuation should be before numbers and letters. However, I understand that there are cases in which you might want a custom order.

This can be done with a helper column. For example, if your data starts in cell A1, then put this in cell B1 and drag down to fill.

=SUBSTITUTE(A1;"-";"z")

Then select columns A and B, go to Data -> Sort and sort by column B. When done, delete column B.

If this is not good enough then you will need to write a macro function that inputs a string and returns a sort key. An example is at .