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:

- Code: Select all Expand viewCollapse view
`Text`

090

1

10

11

19

9

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:

- Code: Select all Expand viewCollapse view
`Value`

1

9

10

11

19

90

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 topicStoring 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.