Apache OpenOffice Community Forum

MINE IS NOT SOLVED. I have a huge problem sorting my OpenOffice Calc data. Seems like an incomplete sort. Both in Excel and odf format with 2,000 rows. Nothing but some phone number formatting with a format code of [<=9999999]#"-"####;(#") "###"-"#### and cell data. I select my full range with the mouse including column header cells. Then use the "data, sort" for my attempt. I sort on three levels but this problem occurs with only one sort level.

The problem is an ascending sort 1100, 11001,... until about row 1,200 then it starts again with 0001 which should sort at the top. This is not the expected behavior of a data sort. How might I fix this?

Any suggestions are appreciated.

I split this from Topic: [Solved] Sorting Data in Calc which was about a bug in v3.1.0.

A text value '123 (entered with the leading apostrophe) is not the same value as number 123.
Text values are strings of characters and if the characters happen to be digits they are sorted alphabetically: Everything, starting with character "1" belongs together which is wanted for phone numbers.
Number formatting is pointless if the cell value is not a number at all.

Numbers (formatted anyhow or not at all) always sort according to the height of their numeric value like this: No matter how you format the numbers, they always sort according to their value. No formatting attribute (color, border, orientation, number format) will ever change one of your values.

Phone numbers, zip codes and all other kinds of ID numbers should be entered as text values since they will never be used in mathematical context.

Function TEXT converts numbers to numeric strings representing the desired number format:
=TEXT($A2;$X$1) converts the number in A2 using the number format specified in X1.
X1 contains [<=9999999]#"-"####;(#") "###"-"####"
Copy the formula down until the end of the list, copy the formula range, paste-special over the original numbers (text only, no formulas).
Finally, you may prepare the entire column with number format "@", so you supress numeric evaluation for all newly entered phone numbers without typing a leading quote but with leading zeroes and dashes.
Lookups on codes with leading zeroes will work as well as demonstrated in this recent topic

Storing lists with persons and phone numbers in a database solves all those problems by means of pure text columns in combination with pattern controls on input forms, You can use the lists directly in all sorts of office documents (in serial letters and labels, for instance) and you may edit flat address lists in the beamer window (F4) without having to open/import anything.