| |
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:
Select
a cell that has not been formatted.
Select
Format > Cells >
Numbers tab (see Figure 8).
Click
on the desired category.
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.
|
Category
|
Default
format
|
Format
changed when input is different category?
|
|
Number
|
General
|
Yes
|
|
Date
|
Two
defaults based on locale, one for date only and another for date
and time.
|
Yes
|
|
Time
|
Default
for the locale
|
Yes
|
|
Boolean
|
TRUE
or FALSE
|
Yes
|
|
Percentage
|
0.00%
|
No
|
|
Currency
|
Default
for the locale
|
No
|
|
Scientific
|
0.00E+000
|
No
|
|
Fraction
|
#
?/?
|
No
|
Input
category
The
input category is determined as shown in the following table.
Table 5: Input categories
|
Category
|
Nature
of input
|
Examples
(based
on USA locale)
|
|
Function
or formula
|
Starts
with an equals sign.
|
=A1+A2
or
=SUM(A2:A10)
|
|
Boolean
|
True
or false (case does not matter)
|
True
or
FALSE
|
|
Number
|
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
|
|
Percentage
|
Same
as for number but has a % at right end.
|
1%,
-1%, (1)% 1.1% , 0.1%
|
|
Currency
|
Same
as for Number but with the locale’s currency symbol at either
end.
|
$1,
1$, $(1), (1)$, $1.1
|
|
Scientific
|
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)
|
|
Fraction
|
Optional
negative sign, some digits, a space, some digits, a slash, some
digits.
|
1
1/2, -1 2/3
|
|
Time
|
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
|
|
Date
|
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 > OpenOffice.org > 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
|
|
Text
|
Anything
that does not meet the above criteria.
|
28/28
|
Changing
the cell’s format based on input
Calc
works as follows:
First
a category is worked out for the input.
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.
else:
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.
else:
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.
|
|
