Text Functions

The functions that relate to handling text are:

 

CHAR(value)

This function returns the character specified by a number.

example: CHAR(66) gives the result B

 

CLEAN(text)

This function removes all non-printable characters from text.

 

CODE(text)

This function returns a numeric code to represent the first character in a text string. The returned code corresponds to the Windows character set (ANSI).

example: CODE(""B"") gives the result 66

 

CONCATENATE(text1,text2)

This function combines multiple text strings or numbers into one text string.

 

DOLLAR(value,digits)

This function converts a number to text using currency format, with the decimals rounded to the specified place.

argument

description

value

Numeric value to convert to text using the currency format

digits

[Optional] Number of decimal places to maintain; if negative, the value is rounded to the left of the decimal point; if omitted, the function rounds to two decimal places

example: DOLLAR(123.45,1) gives the result $123.5

 

EXACT(text1,text2)

This function returns true if two strings are the same; otherwise, false.

 

FIND(findtext,intext,start)

This function finds one text value within another and returns the text value's position in the text you searched.

 

FIXED(num,digits,notcomma)

This function rounds a number to the specified number of decimal places, formats the number in decimal format using a period and commas (if so specified), and returns the result as text.

argument

description

num

Number to round and convert to text

digits

[Optional] Number of decimal places; if omitted, uses two places

notcomma

[Optional] Logical value whether not to use commas; if omitted or FALSE, returns with commas

 

LEFT(mytext,num_chars)

This function returns the specified leftmost characters from a text value.

argument

description

mytext

Text string that contains the characters you want to extract.

num_chars

[Optional] Number of characters to extract; if omitted, uses one; if not an integer, the number is truncated

example: LEFT(""Total Price"", 5) gives the result Total

 

LEN(value)

This function returns the length of, the number of characters in, a text string.

 

LOWER(string)

This function converts text to lower case letters.

 

MID(text,start_num,num_chars)

This function returns the requested number of characters from a text string starting at the position you specify.

 

PROPER(text)

This function capitalizes the first letter in each word of a text string.

 

REPLACE(old_text,start_char,num_chars,new_text)

This function replaces part of a text string with a different text string.

 

REPT(text,number)

This function repeats text a specified number of times.

 

RIGHT(text,num_chars)

This function returns the specified rightmost characters from a text value.

 

SUBSTITUTE(text,old_text,new_text,instance)

This function substitutes a new string for specified characters in an existing string.

 

T(value)

This function returns the text in a specified cell.

 

TRIM(text)

This function removes extra spaces from a string and leaves single spaces between words.

 

UPPER(string)

This function converts text to uppercase letters.

 

VALUE(text)

This function converts a text string that is a number to a numeric value.

 

 

 

See Also:


Formula Functions