Text Functions
Function | Description |
LEN | LEN returns the number of characters in a text string. |
LEFT | LEFT returns the first character or characters in a text string, based on the number of characters you specify. |
RIGHT | RIGHT returns the last character or characters in a text string, based on the number of characters you specify. |
LOWER | Converts all uppercase letters in a text string to lowercase. |
UPPER | Converts all lowercase letters in a text string to uppercase. |
TRIM | Removes all spaces from text, except for single spaces between words. |
MID |
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |
CONCATENATE | Joins two or more text strings into one string. |
REPLACE | REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. |
FIND | Locates one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. |
EXACT | Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case sensitive function. |
SPLIT | Breaks a delimited text into collection of sub-strings. |
Math functions
Function | Description |
ABS | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
AVERAGE | Returns the average of two or more numbers. |
CEILING | Rounds a number to the nearest integer. |
FLOOR | Rounds number down. |
MIN | Returns the smallest number in a list of values. |
MAX | Returns the largest value in a list of values. |
SUM | Returns the amount of all values in a list of values. |
Logical functions
Function | Description |
ISEVEN | Returns TRUE if number is even, or FALSE if number is odd. |
ISODD | Returns TRUE if the number is odd, FALSE otherwise. |
ISTEXT | Returns TRUE if the value is text, FALSE otherwise. |
ISBLANK | Returns TRUE if the value is blank, FALSE otherwise. This function works best with text and numbers. It is not applicable to Date, DateTime and Boolean data types. |
ISNUMBER | Returns TRUE if the value is a number, FALSE otherwise. |
Logical Expressions
Function | Description |
NOT | Reverses the logic of its argument. |
EQUAL | Compares if two values have the same value and returns TRUE, FALSE otherwise. |
NOT EQUAL | Compares if two values have the different value and returns TRUE, FALSE otherwise. |
GREATER | Compares if one value is greater than other and returns TRUE, FALSE otherwise. |
GREATER OR EQUAL | Compares if one value is greater than or equal to other value and returns TRUE, FALSE otherwise. |
LESS | Compares if one value is less than other value and returns TRUE, FALSE otherwise. |
LESS OR EQUAL | Compares if one value is less than or equal to other value and returns TRUE, FALSE otherwise. |
AND | Applies logical AND operator. |
OR | Applies logical OR operator. |
IF THEN ELSE | Conditionally executes a statements, depending on the value of an expression in IF clause. |
Constants
DATA TYPE | VALUE |
Date | #1/1/2017# |
Boolean | TRUE FALSE |
Text | “STRING” |
Number | 1 1.0 |
Date and DateTime functions
Function | Description |
DATE | Creates Date from three values representing day, month and a year. |
TODAY | Returns the current date. |
YEAR | Returns the year corresponding to a date. |
MONTH | Returns the month of a date represented by the argument. |
DATEDIF | Calculates the number of days between two dates. |
Conversion functions
Function | Description |
TODATE | Converts text to date value with option to specify format and culture code of the string value. |
DATETOTEXT | Converts date to text representation using format and culture code. |
TONUMBER | Converts text to number value with option to specify culture code of the string value. |
NUMBERTOTEXT | Converts number to text representation using format and culture code. |
TRUNC | Truncates a number to an integer by removing the fractional part of the number. |
Arithmetical operations
Function | Description |
PLUS | Adds the values of the operands. |
MINUS | Subtracts the values of the operands. |
DIVIDE | Divides its first operand by its second operand. |
MULTIPLY | Multiplies its first operand by its second operand. |
DIVIDE WITH REMAINDER | Returns the remainder after dividing its first operand by its second. |
Miscellaneous
Function | Description |
TOARRAY | Combines all its arguments into collection of objects. The arguments might be of different type. Use this function in combination with MATCH. |
MATCH | Searchers value in array (collection) of objects and returns its index into the array. The matched object must be of the same type as the value. If no match is found, the result is -1. Use this function in combination with TOARRAY. |