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 character 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 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"
Returns a specified number of characters starting at the given 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 (starting at the given position start), for the given number of characters number. 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 ("text") within another given text string ("data"). The search is case-insensitive.
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 requested element (from 1 onwards). You can pass a negative element to return an element counting from the end.
SPLIT(data, char, 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 (0-9) and not a letter (a-z or A-Z).
STRIP(data)
Example:
STRIP("A & B9 = C;") returns "AB9C"
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 account settings). 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
n - the number of decimals (n = 1 through 8)
"D" -
date
"0.0%" - percent
"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 dictated 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, whilst inserting the given separator text (char).
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"
Returns the value that is passed as a parameter, but validated against its data type. This is mostly used in imports to define constant values.
VALUE(data)
Example:
VALUE("price") returns "price"