Sort Numerically in OO Calc functions alphabetically

Question

Using Open Office 3.3.0

Calc will sort a list of numbers not in numerical fashion, as would seem reasonable, logical, and rational, but in alphabetical order.

Eg: Calc thinks that 103,1000,etc come between 1 and 2.

If I change the format to of the cells to Number, it makes no difference; they remain in the order of 1, 100, 1000, 2, 250, 3.

A work around has been posted on OO forums to create a 2nd column, and use =value(A1), which calculates the numerical value of the original column, but I found if I sorted data using this 2nd column as the sort order it still sorted alphabetically.

I'm not sure how this fell through the cracks. I would assume numbers and sorting numerically would be important for a program called Calc, instead of treating numbers like letters.

Update: as much as I admire OO for creating a pretty decent application suite, their support pretty much sucks. Every time I've attempted to login to submit a bug, I get a 404 error. If I forget my password to their support forums, I can only retrieve it if I remember both my username and email. If I forget my username, then game over, because I cannot register again with the same email. /end sooky rant.

Answer 1

The sort order applied by OOo.calc depends on the cell's content. If the cell holds textual content, calc correctly applies alphabetical sorting; if the content is numeric, sorting is based on the numeric value.

A problem may arise if the content seems to be numeric but in fact is textual; this may happen if you copy&paste content from other applications or from web pages into a calc table.

There's an easy solution for those situations: execute a search/replace using regular expressions. Search for

^.*$

and replace with

&

To make sure that the find/replace expression is interpreted as a regex, click on the "More" Button and make sure "Regular expressions" is selected.

This effectively replaces each cell's content with itself, but drops the ' apostrophe that marks cell content as text. As result, the "textual" numbers are converted into numerical values, allowing calc to sort according to the numerical values.

So, starting with this content (each cell contains a string!)...

... and sorting the cells will lead to this result:

Executing the search/replace as described changes the content to numerics...

... allowing for a nice numeric sort: