These functions perform an operation on text values, and return a derivative of the original values. You can use these to convert text values in imports, reports, or models.
Returns the character that corresponds to the given numeric value.
CHAR(data)
Example: CHAR(65) returns "A"
Returns a text string with all whitespaces and nonprintable characters removed from the given text.
CLEAN(data)
Example: CLEAN("A B C ") returns "ABC"
Returns the numeric code for the given start position within the given text string. If you don't provide the character position, the first character will be used.
CODE(data, start)
Example: CODE("ABC", 2) returns 66
Joins together two or more given text strings.
CONCAT(data, text)
Example:
data = "this text"
CONCAT(data, " and more") =
"this text and more"
Returns true if two text strings are exactly the same, else false. With the optional type parameter you can ignore case-sensitivity, spaces, and special characters.
EXACT(data, text, type)
Type:
0 = exact match (default)
1
= ignore case-sensitivity, spaces, and special characters
Example:
data = "this text"
EXACT(data, "This Text!") = false
EXACT(data,
"This Text!", 1) = true
Returns a specified number of characters from the start of the given text string.
LEFT(data, number)
Example:
data = "this text"
LEFT(data, 3) = "thi"
Returns the length (i.e. the number of characters) of the given text string.
LEN(data)
Example:
data = "this text"
LEN(data) = 9
Converts all characters in the given text string to lower case.
LOWER(data)
Example:
data = "THIS TEXT"
LOWER(data) = "this text"
Merges the values from a key-value data range into placeholders inside the given text. The data range needs to consist of 1 or more rows with 2 columns, the 1st column being the key and the 2nd column being the associated value. The text can contain multiple instances of key embedded between square brackets (e.g. "The revenue is [amount]" ).
Please note that this function does not follow the cell format (i.e. number and date format) of the placeholder value. You have to use the TEXT() function in the placeholder value cell to set the desired presentation format for amounts and dates.
You can also use 2 special placeholders for text markup: [br] for a line break and [li] for a list of bullet points.
MERGE(data, text)
Example:
=MERGE(A2:B5,"We close the year with revenue of $ [revenue]
and costs of $ [costs].")
Examples:
=MERGE(A2:B5, IF(B6>1000, D1, D2))
You can dynamically retrieve the text from a data query:
=MERGE(A2:B5, DGET("ModelA", 1))
=MERGE(A2:B5, DGET(IF(B6>1000, "ModelA", "ModelB"), 1))
Equally, you can retrieve the values from a data query (it must return exactly
2 columns). For example:
=MERGE(DRANGE("2019", 1), "The revenue is [amount]")
=MERGE(DRANGE("", 1, 0, -1), "The revenue is [amount]")
Returns a specified number of characters starting at the given start position within the given text string. The number parameter is optional.
MID(data, start, number)
Example:
data = "this text"
MID(data, 2, 3) = "his"
Converts all words in the given text string to proper case (letters that do not follow another letter are upper case and all other characters are lower case). The optional split parameter will separate capitalized words with a space.
PROPER(data, split)
Example:
data = "this text"
PROPER(data) = "This Text"
Replaces all or part of the given text string with another string new (at the given start position), for the given number of characters. The start and number parameters are optional.
REPLACE(data, start, number, new)
Example:
data = "this text"
REPLACE(data, 1, 4, "that") =
"that text"
Returns a text string with the given text string repeated for the given number of times.
REPT(data, number)
Example:
data = "ab."
REPT(data, 3) returns "ab.ab.ab."
Returns a specified number of characters from the end of a given text string.
RIGHT(data, number)
Example:
data = "this text"
RIGHT(data, 3) = "ext"
Returns the first position of the given text string or character within another given data string. The search is case-insensitive. You can optionally specify a start position.
SEARCH(data, text, start)
Example:
data = "this text"
SEARCH(data, "text", 1) returns 6
Splits the given text string into separate elements, based on the given delimiter character, and returns the element at the given index (starting at 1). You can pass a negative index to return an element counting backwards from the end.
This function is the reverse of the TEXTJOIN function.
SPLIT(data, delimiter, index)
Example:
data = "apple/orange/banana"
SPLIT(data, "/", 2) =
"orange"
SPLIT(data, "/", -3) = "apple"
Returns a text string with all characters removed from the given text string that are not numeric (digits 0-9) and/or not a letter (a-z or A-Z).
STRIP(data, type)
Type:
0 = return digits and letters(default)
1 = return only numeric digits
2 = return only letters (case-insensitive)
3 = return only lowercase letters
4 = return only uppercase letters
Example:
STRIP("Account!Nr#400.10;") returns "AccountNr40010"
STRIP("Account!Nr#400.10;",
1) returns 40010
Substitutes all occurrences of the given search text (old) within the given text string (data), with the given replacement text (new).
SUBSTITUTE(data, old, new)
Example:
data = "this text"
SUBSTITUTE(data, "this", "that")
= "that text"
Converts the given value into text, using a user-specified format (as per the user profile). You can use this function if you want to represent values as formatted text (for example, when you want to concatenate it with other text).
TEXT(data, type)
Type:
"0" = no decimals (default)
"0.0" = 1 decimals
"0.00" = 2 decimals
"0.0%" = percent with 1 decimal
1-9 = the number of decimals (n = 1 through 8)
"D" = date
"Zn" = pad with leading zeros (n = fixed length)
Examples:
data = 4"
TEXT(data, "0.00") returns 4.00
TEXT(data, 2) returns 4.00
TEXT(data,
"0.0%") returns "4.0%"
TEXT(data, "Z6") returns "000004"
You can use this function in imports, reports, and models. However, in models the visual presentation of numeric values is determined by the "cell format", so please make sure your cell format is set accordingly.
Joins together the given text strings in a cell range or data array, merged with inserting the given text (char).
This function is the reverse of the SPLIT function.
TEXTJOIN(data, char)
Example:
data = ["a", "b", "c"]
TEXTJOIN(data, "/") =
"a/b/c"
Removes redundant spaces and spaces at the start and end of the given text string.
TRIM(data)
Example:
data = " this text "
TRIM(data) = "this text"
Converts all characters in the given text string to upper case.
UPPER(data)
Example:
data = "this text"
UPPER(data) = "THIS TEXT"