|
In support of year 2000 compliance, all years in dates may be designated as yyyy instead of yy. For backwards compatibility, conversion keywords 1-16, yy (2-digit years) automatically have 1900 added. For conversion keywords 17-55, yy < threshold automatically have 2000 added; yy ≥ threshold automatically have 1900 added.
|
|
The year 2000 threshold value is set in the Data Cleaning tab of the Advanced Settings window (invoke using Options > Settings > Advanced Settings).
|
|
If you change the value year 2000 threshold value, you must update all macro functions using the NUMBER macro function to manipulate date values with 2-digit years. To force an update of a macro function, you can make any edit (for example, adding a space and deleting it) and clicking the check mark icon to accept the change.
|
|
When using format 0, only the first five characters of each text string are used to generate a unique number. All strings with the same first five characters will be translated to the same numeric value. The same text string will produce the same numerical value every time, even across different spreadsheets. If required, use string macros to manipulate strings so that the first five characters uniquely define a class. Note that the resulting numerical values may be very small. Use the Display Formats window to either increase the number of decimal places displayed, or change the format to exponential mode ( 00E+00).
|
TEMP = NUMBER("$1.23") or TEMP = NUMBER("123%", 2)
|
Creates a new column named TEMP containing the numeric values of the text strings in column V1. Any dollar values are correctly converted into numerical values. ??? 's returned for text strings that cannot be parsed using the $ format.
|
Creates three new columns named TEMP, VX, and VY. The column TEMP contains the numerical values of text strings in column V1. The column VX contains the numerical values of text strings in column V2. The column VY contains the numerical values of text strings in column V3. Any dates in the format dd-mmm-yy are converted into the number of days offset from January 1, 0000. ??? 's are returned for text strings that cannot be parsed using the $ format.
|
Creates two new columns named TEMP and VX. The column TEMP contains the numerical values of text strings in rows 10-20 of column V1. The column VX contains the numerical values of text strings in rows 10-20 column V2. All standard three character representations of days of the week are converted into the numbers 0-6 (0 = Sunday, 6= Saturday). If there is no match for a weekday name, ??? is returned.
|
Assuming that column V1 contains all 5-digit text strings, creates one new column named TEMP containing a different numerical value for each unique string.
|
Copyright IBM Corporation 2013. All Rights Reserved.
|