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") → 0EXACT("Fox"; "Fox") → 1EXACT(""; 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") → 0MATCH(" Blocker "; "blocker") → 1MATCH("Hamster"; "ham*") → 1MATCH("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) → 20SEARCH("Jedi*"; "Return of the Jedi") → 15SEARCH("/^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. The locale of the current user is applied.
| Parameter | Type | Description |
|---|---|---|
Value | Text/Each | The text to manipulate. |
| → Result | Text | Returns Value in all uppercase. |
Example:
UPPER("ham") → "HAM"