Text

Text

Text strings are stored in unicode format and allow up to 32767 characters as cell values. In formulas there is a total length restriction of 8192 characters. Text is converted to number according to regional number formats including date, time and currency settings as well as exponents (E or **), percentages (2% or %2) and fractions (in fixed format). Leading or trailing spaces, tabs and line feeds are ignored in numeric conversions.

In text criteria a non-breaking hyphen (U+0173) may be inserted to avoid automatic numeric conversion. For database criteria if the criteria string begins with a test for equality (= or <>) the entire string is matched otherwise all strings that begin with that criteria are matched. For other inequalities (> >= <= <) the order follows a unicode collation sequence, for which the language convention is set in windows regional settings.

Text ordering can be broken down into several levels. First in the order come symbols and other special characters, then numerals and then letters of the alphabet. Latin script comes before Greek, Hebrew and other languages. Diacritics and letter variations are distinguished at a second level. The punctuation marks apostrophe (') and hyphen (-) are only distinguished at a third level. Capitalisation is the final level of comparison in range sorting, but is not taken into account for text criteria which are case insensitive. For example in the first line below is a list sorted as a binary string (character code) from left to right whereas in the second line below is the same list sorted according to string convention:

Binary	String
Bill's	Billet
Billet	Bills
bills	bills
can’t	cannot
cannot	can't
Cant	Cant
co-op	Con
con	coop
coop	co-op