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.

  • On a side note, I always have two emails. One for all my junk mail and one for my regular mail. And it also allows me the leeway of having an additional new email if i ever forget my username/password/everything to create a new account. – Jay Jan 10 '12 at 14:27

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.

Find & Replace Dialogue

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!)...

Step 1

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

Step 2

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

Step 3

... allowing for a nice numeric sort:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.