NUMBER macro

The NUMBER macro is available in HCL® Campaign and HCL Interact.

Syntax

NUMBER(data [, conversion_keyword])

Parameters

data

The ASCII text data to convert to numerical values. This can be ASCII text in quotes, a column of text, a cell range containing text, or an expression evaluating to any of the above. For the format definition of data, see the "Macro Function Parameters" section in the chapter in this guide for your HCL product.

conversion_keyword

This optional keyword specifies how to interpret text formats for dates and times. Select one of the keywords in the following table.

Note: If this parameter is not specified, the default is 1.
Conversion Keyword Format Description
0 ##### Converts the first 5 characters of each text string into a unique number
1 $ ( default) Converts dollar values to numerics (for example, "$123.45" to 123.45)
2 % Converts a percentage value to numerics (for example, "50%" to 0.5)
3 mm/dd/yy hh:mm Converts a date and time to the number of days elapsed since January 1, 0000 (1900 is automatically added to the yy year)
4 dd-mmm-yy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the yy year)
5 mm/dd/yy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the yy year)
6 mmm-yy Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (1900 is automatically added to the yy year)
7 dd-mmm Converts a date to the number of days since the beginning of the year (for example, "01-FEB" to 32)
8 mmm Converts a 3-letter month abbreviation to a value between 1-12 (for example, "DEC" to 12)
9 {January | February | March … } Converts a fully spelled-out month name to a value between 1-12 (for example, "March" to 3)
10 {Sun | Mon | Tue … } Converts a 3-day weekday abbreviation to a value between 0-6, where Sunday marks the beginning of the week (for example, "Sun" to 0)
11 {Sunday | Monday | Tuesday … } Converts a fully spelled-out weekday name to a value between 0-6, where Sunday marks the beginning of the week (for example, "Monday" to 1)
12 hh:mm:ss {AM | PM} Converts the time to the number of seconds elapsed since 00:00:00 AM (midnight) (for example, "01:00:00 AM" to 3600)
13 hh:mm:ss Converts the time to the number of seconds elapsed since 00:00:00 AM (midnight) (for example, "01:00:00" to 3600)
14 hh:mm {AM | PM} Converts the time to the number of minutes elapsed since 00:00:00 AM (midnight) (for example, "01:00 AM" to 60)
15 hh:mm Converts the time to the number of minutes elapsed since 00:00:00 AM (midnight) (for example, "01:00" to 60)
16 mm:ss Converts the time to the number of seconds elapsed since 00:00:00 AM (midnight) (for example, "30:00" to 1800)
17 ddmm Converts a date to the number of days since the beginning of the year (for example, "3101" to 31)
18 ddmmm Converts a date to the number of days since the beginning of the year (for example, "31JAN" to 31)
19 ddmmmyy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
20 ddmmmyyyy Converts a date to the number of days elapsed since January 1, 0000 (for example, "31JAN0000" to 31)
21 ddmmyy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
22 ddmmyyyy Converts a date to the number of days elapsed since January 1, 0000 (for example, "31010000" to 31)
23 mmdd Converts a date to the number of days since the beginning of the year (for example, "0131" to 31)
24 mmddyy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
25 mmddyyyy Converts a date to the number of days elapsed since January 1, 0000 (for example, "01010001" to 366)
26 mmm Converts a 3-letter month abbreviation to a value between 1-12 (for example, "MAR" to 3) [Note this is the same as conversion keyword 8]
27 mmmdd Converts a date to the number of days since the beginning of the year (for example, "JAN31" to 31)
28 mmmddyy Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
29 mmmddyyyy Converts a date to the number of days elapsed since January 1, 0000 (for example, "FEB010001" to 32)
30 mmmyy Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
31 mmmyyyy Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (for example, "FEB0001" to 32)
32 mmyy Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
33 mmyyyy Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (for example, "020001" to 32)
34 yymm Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
35 yymmdd Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
36 yymmm Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
37 yymmmdd Converts a date to the number of days elapsed since January 1, 0000 (1900 is automatically added to the year if yy is less than or equal to 20; otherwise 2000 is added)
38 yyyy Converts the year the number of years elapsed since the year 0000 (for example, "1998" to 1998)
39 yyyymm Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (for example, "000102" to 32)
40 yyyymmdd Converts a date to the number of days elapsed since January 1, 0000 (for example, "00010201" to 32)
41 yyyymmm Converts a date to the number of days elapsed between the first of the specified month and January 1, 0000 (for example, "000102" to 32)
42 yyyymmmdd Converts a date to the number of days elapsed since January 1, 0000 (for example, "0001FEB01" to 32)
43 <day>* <month> Converts any delimited date with day followed by month to the number of days elapsed since the beginning of the year (for example, "15-JAN" to 15)
44 <day>* <month>* <year> Converts any delimited date with day appearing before month followed by year to the number of days elapsed since January 1, 0000 (for example, "1/1/0001" to 366)
45 <month>* <day> Converts any delimited date with month followed by day to the number of days since the beginning of the year (for example, "JAN 31" to 31)
46 <month>* <day>* <year> Converts any delimited date with month followed by day followed by year to the number of days elapsed since January 1, 0000 (for example, "JAN 1, 0001" to 366)
47 <month>* <year> Converts any delimited date with month followed by year to the number of days elapsed between the first of the specified month and January 1, 0000
48 <year>* <month> Converts any delimited date with year followed by month to the number of days elapsed between the first of the specified month and January 1, 0000
49 <year>* <month>* <day> Converts any delimited date with month followed by day followed by year to the number of days elapsed since January 1, 0000 (for example, "0001/01/01" to 366)
50 yy Converts the year to the number of years elapsed since the year 0000 (for example, "97" to 97)
51 mm Converts the month to a value between 1-12 (for example, "SEP" to 9)
52 dd Converts the day to a value between 1-31 (for example, "28" to 28)
53 {January | February | March … } Converts a fully spelled-out month name to a value between 1-12 (for example, "March" to 3) [Note this is the same as conversion keyword 9]
54 {Sunday | Monday | Tuesday … } Converts a fully spelled-out weekday name to a value between 1-7, where Sunday marks the beginning of the week (for example, "Sunday" to 1)
55 {Sun | Mon | Tue … } Converts a 3-day weekday abbreviation to a value between 1-7, where Sunday marks the beginning of the week (for example, "Sun" to 1)

Description

NUMBER converts text values in the specified data range into numerical values using the specified format for converting dates and times. If a text string cannot be parsed using the specified conversion_keyword, NUMBER will generate an error. Format 0 converts the first five characters of each text string into different number for each unique text string. This is an easy way to change a column of text into unique classes for outputs to a classifier.

The delimited formats (conversion keywords 43-49) support any of the following as delimiters:

  • / (slash)
  • - (dash)
  • , (comma)
  • " " (space)
  • : (colon)

Months can be represented as mm or mmm; days can be represented as d or dd; years can be represented as yy or yyyy.

Note: 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.
Note: The year 2000 threshold value is set in the Data Cleaning tab of the Advanced Settings window (invoke using Options > Settings > Advanced Settings).
Note: 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.
Note: 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).

Examples

TEMP = NUMBER("$1.23") or TEMP = NUMBER("123%", 2)

Creates a new column named TEMP containing the number 1.23.
TEMP = NUMBER(column("Jan", "Mar", "Dec", 8)

Creates a new column named TEMP containing the numbers 1, 3, and 12.
TEMP = NUMBER("1:52 PM", 14)

Creates a new column named TEMP containing the number 832.
TEMP = NUMBER("1/1/95", 5)

Creates a new column named TEMP containing the number 728660.
TEMP = NUMBER(V1)

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.
TEMP = NUMBER(V1:V3, 4)

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.
TEMP = NUMBER(V1[10:20]:V2, 10)

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.
TEMP = NUMBER(V1, 0)

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.

Related functions

Function Description
WEEKDAY Converts ASCII text date strings to the day of the week