Text Functions

  • 3 minutes to read

This document describes text functions supported by the non-visual Spreadsheet component.

Name

Description

Syntax

BAHTTEXT

Converts a number to Thai text and appends “Baht” to the end of the text.

BAHTTEXT(number)

CHAR

Returns the character specified by a number within the character set used by your system.

CHAR(number)

A number must be between 1 and 255.

CLEAN

Removes all non-printable characters from text.

CLEAN(text)

The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).

CODE

Returns a numeric code for the first character in a text string.

CODE(text)

CONCAT

Combines text from multiple ranges and/or strings without a delimiter.

CONCAT(text1, [text2],…)

CONCATENATE

Joins two or more text strings into one string.

CONCATENATE(text1, [text2], …)

DOLLAR

Converts a number to text using currency format, with the decimals rounded to the specified number of places.

DOLLAR(number, [decimals])

EXACT

Determines whether two strings are exactly the same.

EXACT(text1, text2)

FIND

Returns the starting position of one text string within another text string. The function is case-sensitive.

FIND(find_text, within_text, [start_num])

FIXED

Rounds a number to the specified number of decimals and returns the result as text with or without commas.

FIXED(number, [decimals], [no_commas])

LEFT

Returns a specified number of characters from the start of a text string.

LEFT(text, [num_chars])

LEN

Returns the number of characters in a text string.

LEN(text)

LOWER

Converts all uppercase letters in a text string to lowercase.

LOWER(text)

MID

Returns a specified number of characters from a text string starting at the position you specify.

MID(text, start_num, num_chars)

NUMBERVALUE

Converts text to a number, in a locale-independent way.

NUMBERVALUE(Text, [Decimal_separator], [Group_separator])

PROPER

Converts all characters in a text string to proper case (the first letter in each word is uppercase and all other letters are lowercase).

PROPER(text)

REPLACE

Replaces part of a text string with another string starting from a specified position.

REPLACE(old_text, start_num, num_chars, new_text)

REPT

Repeats text a given number of times.

REPT(text, number_times)

RIGHT

Returns a specified number of characters from the end of a text string.

RIGHT(text,[num_chars])

SEARCH

Returns the position of a character or text string within a specified text string. This function is not case-sensitive.

SEARCH(find_text,within_text,[start_num])

SUBSTITUTE

Substitutes new text for old text in a text string.

SUBSTITUTE(text, old_text, new_text, [instance_num])

T

Returns the text referred to by value. If value does not refer to text, T returns “” (empty text).

T(value)

TEXT

Converts a value to text in a specific number format.

TEXT(value, format_text)

TEXTJOIN

Combines text strings using a specified delimiter.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

TRIM

Removes all spaces from text except for single spaces between words.

TRIM(text)

UNICODE

Returns the number (code point) corresponding to the first character of text.

UNICODE(text)

UPPER

Converts text to uppercase.

UPPER(text)

VALUE

Converts a text string that represents a number to a number.

VALUE(text)

See Also