Text Functions

Text functions let you manipulate character strings.

If a function expects a string but encounters a number, it converts it to a string using mathematical notation ("." decimal separator, no thousands separator).

CONCAT

CONCAT(Value; ...)

Concatenates (merges) text values into a single text.

Parameter	Type	Description
`Value1`, `Value2`, ..., `ValueN`	`Text/Joined`	Text string(s). Accepts any number of arguments. Ignores falsy values.
→ Result	`Text`	A single text containing all the values combined.

Example:

CONCAT(Reporter; ' => '; Assignee)

EXACT

EXACT(A; B)

Checks if text value A is exactly the same as text value B.

Parameter	Type	Description
`A`	`Text/Joined`	Text value.
`B`	`Text/Joined`	Text value.
→ Result	`Boolean`	Returns true (`1`) if values are exactly the same. Otherwise, false (`0`).

This comparison is case sensitive, which is different from comparing A with B using an equals sign or text matching. Undefined values will be equal to each other and to empty strings.

Examples:

EXACT("Fox"; "fox") → 0
EXACT("Fox"; "Fox") → 1
EXACT(""; undefined) → 1

LEFT

LEFT(Value; N)

Returns up to N leftmost characters from a text value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to get characters from.
`N`	`Integer`	The number of characters to get.
→ Result	`Text`	The first `N` characters, starting from the left. If `Value` contains fewer characters, all of them are returned. If the value is less than zero, an empty text is returned.

Example:

LEFT("abc"; 2) → "ab"

LEN

LEN(Value)

Returns the number of characters in a text value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to count. If the value is not text, it is converted to text first.
→ Result	`Integer`	The number of characters in `Value`.

Example:

LEN("abc") → 3

LOWER

LOWER(Value)

Converts text to lowercase. The locale of the current user is applied.

Parameter	Type	Description
`Value`	`Text/Each`	Text to convert.
→ Result	`Text`	`Value` in all lowercase.

Example:

LOWER("HAM") → "ham"

MATCH

MATCH(Value; Pattern)

Checks if the Value matches the Pattern.

Parameter	Type	Description
`Value`	`Text/Joined`	Value to check.
`Pattern`	`Text/Joined`	Pattern to check against. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
→ Result	`Boolean`	Returns `true` (1) or `false` (0).

Examples:

MATCH("Apples"; "Oranges") → 0
MATCH(" Blocker "; "blocker") → 1
MATCH("Hamster"; "ham*") → 1
MATCH("The Flight of the Bumblebee"; "/.light.*beer?/") → 1

MID

MID(Value; Index; Count)

Retrieves a part of the text.

Parameter	Type	Description
`Value`	`Text/Joined`	The text value to get a substring from.
`Index`	`Integer`	The starting index of the part to retrieve, 1-based (first character is at index 1).
`Count`	`Integer`	The number of characters to retrieve.
→ Result	`Text`	Text containing `Count` number of characters, starting from `Index`.

Example:

MID("A quick brown fox"; 3; 5) → "quick"

REPEAT

REPEAT(Value; N)

Produces a text that is a repetition of the string value N times.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to repeat.
`N`	`Integer`	The number of repetitions.
→ Result	`Text`	The repeated text.

Examples:

REPEAT("ha"; 3) → "hahaha"
REPEAT(123, 3) → "123123123"

REPLACE

REPLACE(Value; Pattern; Replacement)

Replaces all occurrences of Pattern with Replacement and returns the new text.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to manipulate.
`Pattern`	`Text`	Pattern to find. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
`Replacement` (Optional)	`Text`	An optional text to use instead of the matched parts. If omitted, the matched parts are removed.
→ Result	`Text`	`Value` with replacements.

Examples:

REPLACE("I like cats"; "CAT"; "DOG") → "I like DOGs"
REPLACE("Can you read this?"; "/[aeuio]/") → "Cn y rd ths?"

REPLACE_AT

REPLACE_AT(Value; Index; Count; Replacement)

Replaces a specific part of the Value with Replacement text and returns the value.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to manipulate.
`Index`	`Integer`	The starting index of the part to replace, 1-based (first character is 1, second is 2, etc.)
`Count`	`Integer`	The number of characters to replace. When Count is 0, the Replacement string gets inserted at the Index position.
`Replacement` (Optional)	`Text`	An optional text to use instead of the replaced part. If omitted, the part will be deleted.
→ Result	`Text`	`Value` with replacements.

When the values of Index and Count are out of range, they are brought to the nearest sensible value.

Examples:

REPLACE_AT("A"; 1; 1; "B") → "B"
REPLACE_AT("What does the fox say?"; 6; 4; "did") → "What did the fox say?"
REPLACE_AT("A step for mankind"; 3; 0; "small ") → "A small step for mankind"
REPLACE_AT("A step for mankind"; 7; 1000) → "A step"

RIGHT

RIGHT(Value; N)

Returns up to N rightmost characters from a string value.

Parameter	Type	Description
`Value`	`Text/Joined`	Text to get characters from.
`N`	`Integer`	The number of characters to get.
→ Result	`Text`	The first `N` characters, starting from the right. If `Value` contains fewer characters, all of them are returned.

Example:

RIGHT("abc"; 2) → "bc"

SEARCH

SEARCH(Pattern; Value; Index)

Finds the first occurrence of a pattern in the value.

Parameter	Type	Description
`Pattern`	`Text`	The text or pattern to look for. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
`Value`	`Text/Joined`	The text to search in.
`Index` (Optional)	`Integer`	Optional parameter that provides an index to start searching at.
→ Result	`Integer`	Returns the index of the matched part (1-based), or `undefined` if not found.

Examples:

SEARCH("ham"; "The Ham is for the Hamster"; 6) → 20
SEARCH("Jedi*"; "Return of the Jedi") → 15
SEARCH("/^Jedi/"; "Not the Jedi you're looking for") → undefined

SPLIT

SPLIT(Value; Separator)

Produces an array from the value by splitting it using a separator.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to split.
`Separator`	`Text`	The text or pattern to split by. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
→ Result	`Array`	Returns an array which contains the split texts.

Examples:

SPLIT("One, Two, Three", ",") → ARRAY("One", "Two", "Three")
SPLIT("A and B or C", "/ and | or /") → ARRAY("A", "B", "C")

SUBSTRING

SUBSTRING(Value; From; To)

Returns a substring, indicated by a starting index and ending index. Note that the indexes are 0-based, unlike in some other functions.

Parameter	Type	Description
`Value`	`Text/Joined`	The text to take the part from.
`From`	`Integer`	Starting index, inclusive, 0 means the first character, `LEN(Value)-1` means the last character.
`To` (Optional)	`Integer`	Optional ending index, exclusive - the character at this index will not be included. If omitted, the substring will include all characters up to the end of the `Value`.
→ Result	`Text`	Returns the portion of the text contained between `From` and `To`.

If To value is greater than the text length, all characters will be included. If To is less than From, an empty text is returned.

Examples:

SUBSTRING("Batman"; 0; 3) → "Bat"
SUBSTRING("Batman"; 3) → "man"

TEXT

TEXT(Value)

Converts value to text. This function is rarely needed, because conversion to text happens automatically when needed.

Parameter	Type	Description
`Value`	`Any (If text, Text/Joined)`	Value to convert
→ Result	`Text`	`Value` converted to text.

Example:

TEXT(1.234) → "1.234"

TRIM

TRIM(Value)

Removes leading and trailing whitespace from the text.

Parameter	Type	Description
`Value`	`Text/Each`	The text to manipulate.
→ Result	`Text`	Returns `Value` without leading/trailing whitespace.

Example:

TRIM(" Batman ") → "Batman"

UPPER

UPPER(Value)

Converts the string to uppercase.

Parameter	Type	Description
`Value`	`Text/Each`	The text to manipulate.
→ Result	`Text`	Returns `Value` in all uppercase.

Example:

UPPER("ham") → "HAM"