Teksti teisendamine arvudeks
Vaikimisi teisendab Calc lahtrites oleva teksti vastavateks arvvÀÀrtusteks, kui ĂŒhetĂ€henduslik teisendus on vĂ”imalik. Kui teisendamine pole vĂ”imalik, tagastab Calc veavÀÀrtuse #VALUE!.
Teisendatakse ainult tĂ€isarvud (sh astendajad) ning ISO 8601 vormingus kuupĂ€evad ja kellaajad oma laiendatud kujul koos eraldajatega. Midagi muud (nt kĂŒmnenderaldajatega murdarve vĂ”i kuupĂ€evi, mis pole ISO 8601 vormingus) ei teisendata, kuna tekstistring sĂ”ltuks lokaadist. Algus- ja lĂ”putĂŒhikuid eiratakse.
Toetatud on jÀrgmised ISO 8601 vormingud:
-
CCYY-MM-DD
-
CCYY-MM-DDThh:mm
-
CCYY-MM-DDThh:mm:ss
-
CCYY-MM-DDThh:mm:ss,s
-
CCYY-MM-DDThh:mm:ss.s
-
hh:mm
-
hh:mm:ss
-
hh:mm:ss,s
-
hh:mm:ss.s
Sajandikoodi CC ei tohi Ă€ra jĂ€tta. KuupĂ€eva ja kellaaja eraldaja T asemel vĂ”ib kasutada tĂ€pselt ĂŒhte tĂŒhikumĂ€rki.
Kui kuupÀev on antud, peab see olema kehtiv Gregoriuse kalendri kuupÀev. Sel juhul peab kellaaeg (pole kohustuslik) jÀÀma vahemikku 00:00 kuni 23:59:59.99999...
Kui antud on ainult kellaajastring, vÔib selle tundide vÀÀrtus olla suurem kui 24, ent minutite ja sekundite suurim lubatud vÀÀrtus on ikka 59.
The conversion is done for single scalar values only, not within ranges.
The conversion is done for single scalar values, as in =A1+A2, or ="1E2"+1. Cell range arguments are not affected, so SUM(A1:A2) differs from A1+A2 if at least one of the two cells contain a convertible string.
Teisendatakse ka valemite sees olevad stringid, nÀiteks ="1999-11-22"+42, mis tagastab kuupÀeva, mis on 42 pÀeva pÀrast 22. novembrit 1999. Arvutused, kus valemites sisalduvad stringidena lokaliseeritud kuupÀevad, tagastavad vea. Lokaliseeritud kuupÀevastringi "11/22/1999" vÔi "22.11.1999" nÀiteks ei saa automaatse teisendamise jaoks kasutada.
When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".
NĂ€ide
In A1 enter the text '1e2 (which is converted to the number 100 internally).
In A2 enter =A1+1 (which correctly results in 101).
The formula =SUM(A1:A2), returns 101 instead of 201 because the conversion does not occur in a range, only for single scalar values. Here, '1e2 is treated as string which is ignored for the SUM function.
=SUM("1E2";1) returns #VALUE! because SUM() and some others that iterate over number sequences explicitly check the argument type.
Changing the default text to number conversion settings
The text to number conversion can be customized in the Detailed Calculation Settings option.