Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Microsoft Office to OpenOffice Migration Guide
Previous Page Home Next Page

Number recognition

Number recognition is where the user types in a “number” and the format of the cell changes accordingly. The same thing applies to text copied from outside Calc and pasted into a spreadsheet.

Rather than compare and contrast Excel with Calc, this section outlines how number recognition works in Calc (which is slightly different from Excel).

Number recognition is dependent on locale. To set the default locale for OOo, use Tools > Options > Language Settings > Languages.

To set the locale for particular cells, select the cells, right-click, and select Format > Cells > Numbers tab > Language.

Default cell formats

The default cell formats are given in Table 4. To determine the default for a locale:

  1. Select a cell that has not been formatted.

  2. Select Format > Cells > Numbers tab (see Figure 8).

  3. Click on the desired category.

  4. Look for the default named in the Format and Format codes portion of the dialog.


Table 4. Default cell formats for different categories of cell formats.


Default format

Format changed when input is different category?





Two defaults based on locale, one for date only and another for date and time.



Default for the locale









Default for the locale






# ?/?


Input category

The input category is determined as shown in the following table.

Table 5: Input categories


Nature of input

(based on USA locale)

Function or formula

Starts with an equals sign.

=A1+A2 or



True or false (case does not matter)

True or



Digits only with optional decimal separator, optional thousands separator, negative numbers expressed with either a negative sign at either end or enclosed by brackets.

1, 1.1, -1, (1)

1,123, -123.1


Same as for number but has a % at right end.

1%, -1%, (1)% 1.1% , 0.1%


Same as for Number but with the locale’s currency symbol at either end.

$1, 1$, $(1), (1)$, $1.1


Same as for Number but can have E (case does not matter) and then a power of ten.

1e1, 1E1, -1e1, (1e1), (1e-1), (1.2e1)


Optional negative sign, some digits, a space, some digits, a slash, some digits.

1 1/2, -1 2/3


One or two digits, time separator for locale, one or two digits, optional time separator for locale, optional decimal separator for locale, more digits.

10:40, 1:30


Most representations of dates. If the year is not given, it is interpreted as being the current year, if the digits for century are not given, it is determined by the settings in Tools > Options > > General. If the numbers given are not valid for the locale, then input is interpreted as text. Examples include: one or two digits, date separator for locale, one or two digits (1/1); three sets of digits separated by decimal separator (1.1.1); textual representations (1 Feb).

1/1, 1/1/5, 1.1.5,

1 Feb, 1 Jan,

1 January

1 January 05

Date and time

Input is in the form of (date) digits, date separator for locale, a space, digits with time separator for locale. As for dates, the numbers must be valid for the locale.

1/1/5 10:30


Anything that does not meet the above criteria.


Changing the cell’s format based on input

Calc works as follows:

  1. First a category is worked out for the input.

  2. If the input is a function and the current cell’s format is a default, that is, not explicitly defined, then the cell’s format is determined by the hierarchy of the inputs to the function. The order is dates, time, currency, percentage, scientific, fractions, and numbers unless an input to a function is returning a value of a different type. Some examples of this last exception:

    • A date plus a number returns a date, because date supersedes number in the hierarchy.

    • A function like =Month() returns a number even though a date is used as the input.


  • If the input category is boolean, then the cell’s format is changed to boolean. If the input category is text, then the cell’s format does not change.


  • If the cell format is Number–general, Date–default, Time–default, or boolean, and the category of input belongs to a different category, then the cell’s format is changed to the default for the input category.

    Pasting values

    A consequence is that, when copying text that contains dates and pasting into a Calc spreadsheet, you can determine what the result is going to be. Suppose your default locale is USA where the default date format is MM/DD/YY and the data that you are copying is in the format DD/MM/YY (the European default). If you set the format of the cells that you are going to paste the data into to date format DD/MM/YY, the dates will be correctly interpreted; if you do not, it will not. You can then change the format of the cells to MM/DD/YY to display the dates in USA format.

    Microsoft Office to OpenOffice Migration Guide
    Previous Page Home Next Page

      Published under the terms of the GNU General Public License Design by Interspire