Text
You can give a property a dynamic value that is automatically updated based on an expression you provide. This allows you to combine the values of other properties into one, or to perform a mathematical formula on values.
Other properties can be included in the expression by using their ID.
Note that the values of computed properties will be calculated automatically and therefore can not be modified manually. However you can always make the property non-computed again by switching off the "Computed" toggle.
Returns the content of an array at a given index, starting from 1.
Syntax
INDEX(array, index)
- array: A reference to a property containing a list of values.
- index: The index of the element in array that needs to be returned, starting from 1.
Sample Usage
INDEX(numbers, 1)
- Returns 1 for numbers = [1, 2, 3]
Returns the previously computed value for the current property.
Syntax
ANS()
Sample Usage
COALESCE(ANS(), TODAY())
- Keeps the creation date regardless of how many times the value gets recomputed.
Returns the sum of two numbers.
Syntax
value1 + value2
- value1: The first addend.
- value2: The second addend.
Sample Usage
property1 + 3
3 + 4
Returns TRUE if two specified values are equal and FALSE otherwise.
Syntax
value1 = value2
- value1: The first value.
- value2: The value to test against value1 for equality.
Sample Usage
property = "xyz"
2 = 3
Returns the concatenation of two values.
Syntax
value1 & value2
- value1: The value to which value2 will be appended.
- value2: The value to append to value1.
Sample Usage
property & 12
"Mr. " & name
Notes
- value1 and value2 can be any scalar value or reference to a scalar value, including numeric and text types.
Returns one number divided by another.
Syntax
dividend / divisor
- dividend: The number to be divided.
- divisor: The number to divide by (cannot equal 0).
Sample Usage
4 / 2
number1 / number2
Returns a number raised to a power.
Syntax
base ^ exponent
- base: The number to raise to the exponent power. If base is negative, exponent must be an integer.
- exponent: The exponent to raise base to.
Sample Usage
4 ^ 0.5
property1 ^ property2
Returns TRUE if the first argument is strictly greater than the second, and FALSE otherwise.
Syntax
value1 > value2
- value1: The value to test as being greater than value2.
- value2: The second value.
Sample Usage
property > 2
3 > 1
Returns TRUE if the first argument is greater or equal than the second, and FALSE otherwise.
Syntax
value1 >= value2
- value1: The value to test as being greater or equal to value2.
- value2: The second value.
Sample Usage
property >= 2
3 >= 1
Returns TRUE if the first argument is strictly less than the second, and FALSE otherwise.
Syntax
value1 < value2
- value1: The value to test as being less than value2.
- value2: The second value.
Sample Usage
property < 2
3 < 1
Returns TRUE if the first argument is less than or equal to the second, and FALSE otherwise.
Syntax
value1 <= value2
- value1: The value to test as being less than or equal to value2.
- value2: The second value.
Sample Usage
property <= 2
3 <= 1
Returns the product of two numbers.
Syntax
factor1 * factor2
- factor1: The first multiplicand.
- factor2: The second multiplicand.
Sample Usage
property * 2
2 * 3
Returns TRUE if two specified values are not equal and FALSE otherwise.
Syntax
value1 <> value2
- value1: The first value.
- value2: The value to test against value1 for inequality.
Sample Usage
property1 <> property2
2 <> 3
Returns the difference of two numbers.
Syntax
value1 - value2
- value1: The minuend, or number to be subtracted from.
- value2: The subtrahend, or number to subtract from value1.
Sample Usage
property1 - property2
3 - 4
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.
Syntax
AND(logical_expression1, [logical_expression2, ...])
- logical_expression1 - An expression or field containing a value that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.
- logical_expression2, ... - [ OPTIONAL ] - Additional expressions or fields containing values representing some logical values, i.e. TRUE or FALSE, or expressions that can be coerced to logical values.
Sample Usage
AND(firstName = "foo", lastName = "bar")
AND(TRUE, FALSE, TRUE)
Notes
- The number 0 is logically false; all other numbers (including negative numbers) are logically true.
Returns the logical value FALSE.
Syntax
FALSE()
Sample Usage
FALSE()
Returns one value if a logical expression is TRUE and another if it is FALSE.
Syntax
IF(logical_expression, value_if_true, value_if_false)
- logical_expression: An expression or field containing an value that represents some logical value, i.e. TRUE or FALSE.
- value_if_true: The value the function returns if logical_expression is TRUE.
- value_if_false: The value the function returns if logical_expression is FALSE.
Sample Usage
IF(name = "foo", "Name is foo", "Name is not foo")
IF(property, "Property was true", "Property was FALSE")
IF(TRUE, 4, 5)
Notes
- Ensure that value_if_true and value_if_false are provided to the function, and in the correct order.
Evaluates multiple conditions and returns a value that corresponds to the first true condition.
Syntax
IFS(logical_expression, value_if_true, logical_expression2, value_if_true2)
- logical_expression: The first condition to be evaluated. This can be a boolean, a number, an array, or a reference to any of those.
- value_if_true: The returned value if logical_expression is TRUE.
- logical_expression2, value_if_true2, … -: Additional conditions and values if the first one is evaluated to be false.
Sample Usage
IFS(name = "foo", "Name is foo", name = "bar", "Name is bar")
IFS(TRUE, 4)
Notes
- If all conditions are FALSE, #N/A error is returned.
The COALESCE function evaluates its arguments in order and returns the first value that isn't undefined.
Syntax
COALESCE(value1, [value2, ...])
- value1: The first value to evaluate and return if it is not undefined.
- value2, ... - [OPTIONAL]: Additional values to continue evaluating and potentially return if value1 is undefined.
Sample Usage
COALESCE(property1, property2)
- Returns property2 if property1 is undefined, otherwise it returns property1.
COALESCE(ANS(), TODAY())
- Keeps the creation date regardless of how many times the value gets recomputed.
Returns the opposite of a logical value: NOT(TRUE) returns FALSE, NOT(FALSE) returns TRUE.
Syntax
NOT(logical_expression)
- logical_expression: An expression or reference to a property holding an expression that represents some logical value, i.e. TRUE or FALSE.
Sample Usage
NOT(property)
The OR function returns TRUE if any of the provided arguments are logically TRUE, and FALSE if all of the provided arguments are logically FALSE.
Syntax
OR(logical_expression1, [logical_expression2, ...])
- logical_expression1: An expression or reference to a property containing an expression that represents some logical value, i.e. TRUE or FALSE, or an expression that can be coerced to a logical value.
- logical_expression2, ... - [ OPTIONAL ]: Additional expressions or references to properties containing expressions representing some logical values, i.e. TRUE or FALSE, or expressions that can be coerced to logical values.
Sample Usage
OR(firstName = "foo", lastName = "bar")
OR(TRUE, FALSE, TRUE)
OR(0, 1, 2, 3)
Notes
- The number 0 is logically false; all other numbers (including negative numbers) are logically true.
Checks whether the referenced property is empty.
Syntax
ISBLANK(value)
- value: Reference to the property that will be checked for emptiness.
- ISBLANK returns TRUE if value is empty or a reference to an empty property, and FALSE if it contains data or a reference to data.
Sample Usage
ISBLANK(property)
IF(ISBLANK(numericProperty),,5/numericProperty)
Notes
- ISBLANK returns FALSE if the referenced property has any content, including spaces, the empty string (""), and hidden characters. In case of unexpected FALSE results, try clearing the property again to remove any hidden characters.
- This function is most often used in conjunction with IF in conditional statements.
Returns the logical value TRUE.
Syntax
TRUE()
Sample Usage
TRUE()
Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.
Syntax
SWITCH(expression, case1, value1, [case2, value2, ...], [default])
- expression: Any valid values.
- case1: The first case to be checked against expression.
- value1: The corresponding value to be returned if case1 matches expression.
- case2, value2, … - [OPTIONAL]: Additional cases and values if the first one doesn’t match the expression.
- default - [OPTIONAL]: An optional value, specified as the last parameter, to be returned if none of the cases match the expression.
Sample Usage
SWITCH(property, 0, “No”, 1, “Other”)
SWITCH(property, 4, “Four”, 8, “Eight”)
Appends strings to one another.
Syntax
CONCATENATE(string1, [string2, ...])
- string1: The initial string.
- string2, ... - [ OPTIONAL ]: Additional strings to append in sequence.
Sample Usage
CONCATENATE("Super", "calla", "fragi")
CONCATENATE(property1, ", ", property2)
Returns the position at which a string is first found within text, case-sensitive.
Syntax
FIND(search_for, text_to_search, [starting_at])
- search_for: The string to look for within text_to_search.
- text_to_search: The text to search for the first occurrence of search_for.
- starting_at - [ OPTIONAL - 1 by default ]: The character within text_to_search at which to start the search.
Sample Usage
FIND("n", property1)
FIND("wood", "How much wood can a woodchuck chuck", 14)
Notes
- FIND is case-sensitive, meaning that uppercase and lowercase letters matter. For example, "abc" will not match "ABC".
- Ensure that search_for and text_to_search are not supplied in reverse order. The arguments are supplied in a different order than other text functions such as SPLIT and SUBSTITUTE.
Concatenate several elements (values or list of values) using a specified delimiter.
Syntax
JOIN(delimiter, value1, [value2, ...])
- delimiter: The character or string to place between each concatenated value.
- value1: The value or values to be appended using delimiter.
- value2, ... - [ OPTIONAL ]: Additional value or values to be appended using delimiter.
Sample Usage
JOIN(", ", firstName, lastName)
JOIN(", ", mainEmail, secondaryEmails)
Returns the length of a string.
Syntax
LEN(text)
- text: The string whose length will be returned.
Sample Usage
LEN(name)
LEN("lorem ipsum")
Notes
- LEN counts all characters, even spaces and nonprinting characters. In cases where LEN returns unexpected values, ensure that there are no such characters in text.
Divides text around a specified character or string, and outputs an array of the separate strings.
Syntax
SPLIT(text, delimiter)
- text: The text to divide.
- delimiter: The character or characters to use to split text.
- By default, each character in delimiter is considered individually, e.g. if delimiter is "the", then text is divided around the characters "t", "h", and "e".
Sample Usage
SPLIT("1,2,3", ",")
SPLIT("Alas, poor Yorick", " ")
SPLIT(property, ",")
Notes
- Note that the character or characters to split the string around will not be contained in the result themselves.
Converts a specified string to lowercase.
Syntax
LOWER(text)
- text: The string to convert to lowercase.
Sample Usage
LOWER("LOREM IPSUM")
LOWER(property)
Extracts matching substrings according to a regular expression.
Syntax
REGEXEXTRACT(text, regular_expression)
- text: The input text.
- regular_expression: The first part of text that matches this expression will be returned.
Sample Usage
REGEXEXTRACT("Needle in a haystack", ".e{2}dle")
Notes
- This function only works with text (not numbers) as input and returns text as output. If numbers are used as input, convert them to text using the TEXT function.
Whether a piece of text matches a regular expression.
Syntax
REGEXMATCH(text, regular_expression)
- text: The text to be tested against the regular expression.
- regular_expression: The regular expression to test the text against.
Sample Usage
REGEXMATCH("Spreadsheets", "S.r")
Notes
- This function only works with text (not numbers) as input and returns a logical value, i.e. TRUE or FALSE, as output. If numbers are used as input, convert them to text using the TEXT function.
Replaces part of a text string with a different text string using regular expressions.
Syntax
REGEXREPLACE(text, regular_expression, replacement)
- text: The text, a part of which will be replaced.
- regular_expression: The regular expression. All matching instances in text will be replaced.
- replacement: The text which will be inserted into the original text.
Sample Usage
REGEXREPLACE("Spreadsheets", "S.*d", "Bed")
Notes
- This function only works with text (not numbers) as input and returns text as output. If numbers are used as input, convert them to text using the TEXT function.
Replaces existing text with new text in a string.
Syntax
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
- text_to_search: The text within which to search and replace.
- search_for: The string to search for within text_to_search. search_for will match parts of words as well as whole words; therefore a search for "vent" will also replace text within "eventual".
- replace_with: The string that will replace search_for.
- occurrence_number - [ OPTIONAL ]: The instance of search_for within text_to_search to replace with replace_with. By default, all occurrences of search_for are replaced; however, if occurrence_number is specified, only the indicated instance of search_for is replaced.
Sample Usage
SUBSTITUTE("search for it", "search for", "Google")
SUBSTITUTE(address, "new york", "New York")
SUBSTITUTE("January 2, 2012", 2, 3, 1)
Notes
- SUBSTITUTE can be used to replace one or all instances of a string within text_to_search. It cannot be used to replace multiple, but not all instances within a single call.
- This function returns text as the output.
Removes leading, trailing, and repeated spaces in text.
Syntax
TRIM(text)
- text: The string or field containing a string value to be trimmed.
Sample Usage
TRIM("lorem ipsum ")
TRIM(property)
Notes
- It is important to use TRIM when text is used in formulas or data validation because spaces in front of or after the text are significant.
- TRIM removes all spaces in a text string, leaving just a single space between words.
Converts a specified string to uppercase.
Syntax
UPPER(text)
- text: The string to convert to uppercase.
Sample Usage
UPPER("lorem ipsum")
UPPER(property)
Converts a number into text according to a specified format.
Syntax
TEXT(number, format)
- number: The date or time to format.
- format: The pattern by which to format the number, enclosed in quotation marks.
Sample Usage
TEXT(date, "dddd, MMMM Do YYYY, h:mm:ss a")
TEXT(DATE(year, 7, 20), "YYYY-MM")
Notes
Returns the phone number converted to international format (with spaces). If the operator is unable to convert the value, the forwarded data will be returned.
Syntax
FORMATPHONE(phone)
- phoneNumber: Phone number to be converted to international format.
Sample Usage
FORMATPHONE("+3293539020") ==> +32 9 353 90 20
FORMATPHONE("+3289392161") ==> +32 89 39 21 61
FORMATPHONE("+32") ==> +32
FORMATPHONE("some text value") ==> some text value
Slugifies a specified string (lowercased, hyphenated).
Syntax
SLUG(text)
- text: The string to slugify.
Sample Usage
SLUG("LOREM IPSUM")
SLUG(property)
