TypeCast
The TypeCast() function takes a range as input and casts the columns or rows of that range according to a list of data types. The data types correspond to the columns or the rows, depending on the selected mode. This function is used when working with raw data.
Syntax
TypeCast (Range, Data Types, Mode, Cast Error Value)
| Range | The range of cells in Excel used as input |
| Data Types | A comma separated list of data types to use for casting the rows or columns. One data type is required for each row or column. Valid data types: text or t; number or n; date or d; boolean or b. |
| Mode | The mode determines whether to apply the casting to the rows or columns. The default is columns. |
| Cast Error Value | The value to use when a cast fails. The default is #N/A. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Typecasting input columns as numbers, dates, text and boolean values
Example 2. Typecasting input rows as numbers, dates, text and boolean values
Example 3. Providing specific error text to use when a type cast fails
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Typecasting input columns as numbers, dates, text and boolean values
The Typecast() function is used to cast the values in the columns as numbers, dates, text and boolean values. Note: the default mode is columns; in this example the parameter value has been omitted, so the columns are cast as the specified types.
| Range | Typecast the columns | |||
| 1900/01/10 | 10 | 2 | 1 | =TypeCast(A2:D7,”number,date,text,boolean”) |
| 1900/01/30 | 20 | A1 | 0 | |
| 3 | 7800 | 4 | 1 | |
| 4 | 40 | 2 | 0 | |
| 1900/02/09 | 12000 | 2 | AB | |
| 6 | 60 | 5 | 0 | |
| Range | Output | ||||||
| 1900/01/10 | 10 | 2 | 1 | 10 | 1900/01/10 | 2 | TRUE |
| 1900/01/30 | 20 | A1 | 0 | 30 | 1900/01/20 | A1 | FALSE |
| 3 | 7800 | 4 | 1 | 3 | 1921/05/09 | 4 | TRUE |
| 4 | 40 | 2 | 0 | 4 | 1900/02/09 | 2 | FALSE |
| 1900/02/09 | 12000 | 2 | AB | 40 | 1932/11/07 | 2 | #N/A |
| 6 | 60 | 5 | 0 | 6 | 1900/02/29 | 5 | FALSE |
Example 2. Typecasting input rows as numbers, dates, text and boolean values
The Typecast() function is used to cast the rows as numbers, dates, text and boolean values. Note that in this example, the short forms of the types are used, in other words ‘n’ for number and ‘d’ for date, etc.
| Range | Typecast the rows | |||
| 4 | 10 | 2 | 1 | =TypeCast(A2:D5,”n,d,t,b”,”row”) |
| 1900/01/30 | 20 | 7800 | 0 | |
| aa | bb | cc | 23 | |
| 0 | 1 | 1 | 1 | |
| Range | Output | ||||||
| 4 | 10 | 2 | 1 | 4 | 10 | 2 | 1 |
| 1900/01/30 | 20 | 7800 | 0 | 1900/01/30 | 1900/01/20 | 1921/05/09 | 1900/01/00 |
| aa | bb | cc | 23 | aa | bb | cc | 23 |
| 0 | 1 | 1 | 1 | FALSE | TRUE | TRUE | TRUE |
Example 3. Providing specific error text to use when a type cast fails
The Typecast() function is used to cast rows as numbers, dates, text and boolean values. Error text is provided to display if the typecast fails.
| Range | Typecast the rows and replace default error text | |||
| 4 | Q | 2 | 1 | =TypeCast(A2:D5,”n,d,t,b”,”row”,”- INVALID -“) |
| 1900/01/30 | 20 | 7800 | 0 | |
| aa | bb | cc | 23 | |
| 0 | 1 | Q | 1 | |
| Range | Output | ||||||
| 4 | Q | 2 | 1 | 4 | – INVALID – | 2 | 1 |
| 1900/01/30 | 20 | 7800 | 0 | 1900/01/30 | 1900/01/20 | 1921/05/09 | 1900/01/00 |
| aa | bb | cc | 23 | aa | bb | cc | 23 |
| 0 | 1 | Q | 1 | FALSE | TRUE | – INVALID – | TRUE |