Functions for text conversion

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.

CHAR

Returns the character that corresponds to the given numeric value.

CHAR(data)

Example: CHAR(65) returns "A"

CLEAN

Returns a text string with all whitespaces and nonprintable characters removed from the given text.

CLEAN(data)

Example: CLEAN("A B C ") returns "ABC"

CODE

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

CONCAT

Joins together two or more given text strings.

CONCAT(data, text)

Example:
data = "this text"
CONCAT(data, " and more") = "this text and more"

LEFT

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"

LEN

Returns the length (i.e. the number of characters) of the given text string.

LEN(data)

Example:
data = "this text"
LEN(data) = 9

LOWER

Converts all characters in the given text string to lower case.

LOWER(data)

Example:
data = "THIS TEXT"
LOWER(data) = "this text"

MID

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"

PROPER

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"

REPLACE

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"

REPT

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."

RIGHT

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"

SEARCH

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

SPLIT

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"

STRIP

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"

SUBSTITUTE

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"

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"

TEXTJOIN

Joins together the given text strings in a cell range or data array, whilst inserting the given separator ("char").

TEXTJOIN(data, char)

Example:
data = ["a", "b", "c"]
TEXTJOIN(data, "-") = "a-b-c"

TRIM

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"

UPPER

Converts all characters in the given text string to upper case.

UPPER(data)

Example:
data = "this text"
UPPER(data) = "THIS TEXT"

VALUE

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"