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"

MERGE ❖ XLReporting

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 of the placeholder value. You have to use the TEXT() function in the placeholder value cell to create 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].")

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 text (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, validated against its data type. This is mostly used in imports to define constant values.

VALUE(data)

Example:
VALUE("price") returns "price"
VALUE("34.0") returns 34