If you do an A-Z sort of cells with symbols and punctuation (!@#$%^&*, etc.) Calc and Excel give different results. Is there any sorting/alphabetizing standard for symbols?
E.g., Unicode collation algorithm. However, any reasonable sorting would be dependent on locale, so maybe your difference is related to different locale settings?
What exactly are the differences for you between Excel and Calc?
I’m having the same issue. I’m getting an Excel file filled with lookup formula, but in Calc some of them don’t work because it doesn’t sort the same way, and so it can’t find the name…
For example, Excel will sort this list:
O’Brien, Liam
O’Connor, Drew
O’Connor, Logan
Oesterle, Jordan
Oettinger, Jake
Okhotiuk, Nikita
Okposo, Kyle
Oleksiak, Jamie
Olivier, Mathieu
Olofsson, Fredrik
Olofsson, Victor
O’Reilly, Ryan
Orlov, Dmitry
If I sort in Calc, I get:
O’Brien, Liam
O’Connor, Drew
O’Connor, Logan
O’Reilly, Ryan
Oesterle, Jordan
Oettinger, Jake
Okhotiuk, Nikita
Okposo, Kyle
Oleksiak, Jamie
Olivier, Mathieu
Olofsson, Fredrik
Olofsson, Victor
Orlov, Dmitry
Notice Excel has sorted O’Reilly as “OR”, while CALC sorts it with the "O’ ". Both the sender and I are using the same Windows localization.
| Excel: | Calc: |
|---|---|
| O’Brien, Liam | O’Brien, Liam |
| O’Connor, Drew | O’Connor, Drew |
| O’Connor, Logan | O’Connor, Logan |
| Oesterle, Jordan | O’Reilly, Ryan |
| Oettinger, Jake | Oesterle, Jordan |
| Okhotiuk, Nikita | Oettinger, Jake |
| Okposo, Kyle | Okhotiuk, Nikita |
| Oleksiak, Jamie | Okposo, Kyle |
| Olivier, Mathieu | Oleksiak, Jamie |
| Olofsson, Fredrik | Olivier, Mathieu |
| Olofsson, Victor | Olofsson, Fredrik |
| O’Reilly, Ryan | Olofsson, Victor |
| Orlov, Dmitry | Orlov, Dmitry |
I suspect it is sorted as “Or,” or even “or”, so not only ignoring the ', but also the difference between “R” and “r”. Otherwise Excel would sort Caps before the other letters and come to the same result as calc.
It is possible that the apostrophe sign in your list was changed after it was posted on the forum. Please upload the list as an .xlsx (.ods) file.
1 Like
No, if you go into editmode of the Post you will see the initial original input. ( in this case probable the c&p conversion from Calc/Excel belongs to @LeroyG )
Here is the file after sorting the list from #4 of this topic in Excel 2019 en_US.
The result is the same as in Calc.
ExcelSort.xlsx (8.7 KB)
1 Like
this site autoformatting.
- 
I copied the text in edit mode. And found no way to add bold or italic to the table.
Sorry for the delay, I’ve been swamped in the last week. I took the original file, remove all the tabs and macros and left only a portion of the database tab that is of interest…
This is the sort order I get from the original, if I sort with LibreOffice, that will change…
SortingIssue.xlsx (7.9 KB)
As expected, the separator character in the file is not the same as the one displayed on the forum.
In the File in O'Brien, Liam the second character has the code U+0027 Apostrophe.
In the above post in O’Brien, Liam, the second character has the code U+2019 Right Single Quotation Mark.
Excel sorts Apostrophe in a special way.
If you use Right Single Quotation Mark in the file, then sorting in Excel and Calc will be the same.
2 Likes
Well that is a “solution”, but that would involve asking people using Excel to change their ways to accommodate the rest of us… If we know there’s an issue, wouldn’t it be better to align ourselves to the “dominant” (i.e. Excel) position? Maybe it can be selected as a toggle, or triggered by .xlsx file import/export? In my case, I’ll try and talk to the creator (but out of the 12 person involved, I’m the only one using LO and having the issue)
Your ideas are reasonable, even more so given that the problem affects the formula results.
However, you may discuss the options how the program should be changed forever here, without any hope to get the result - just because this is not the proper place for enhancement requests and bug reports.
My brief tests indicate, as also suggested in the question from @b.l.zimmerman and the comments from everyone else, that Calc and Excel behave differently, and also that there is a common confusion (driven by software standards/autocorrection) of single quotes (‘ and ’) vs proper apostrophe (').
Calc
Calc assumes that the apostrophe, and also the single quotes which are often used as an automatic replacement character, are considered separate characters to be alphabetized. The characters have their own distinct position in the sorting sequence (which may be locale dependent).
Excel
Excel does the same sorting assumption for the quote characters (which are what we usually get when we think we type the apostrophe), but when encountering a proper apostrophe the sort process will assume that it is a mere “contraction placeholder” and does not take part in sorting, neither as a character by itself nor as a character position placeholder.
Other cases
I have seen other softwares (“database class” applications) where the apostrophe is a placeholder, and equivalent to a space in sort sequence.
What is correct?
I cannot find clear advice either way in style guides (checked APA and MLA), but I haven’t done any deep research, only a brief scan. To me, it seems that the rule for treatment of these characters in sorting is “anything goes”, as long as sorting is consistent within the document.
5 Likes