Text functions

These functions operate on text values. Unless otherwise stated, text functions require text expressions as arguments and produce text results.

  CHAR (Character)
CODE (ASCII Code)
CONCAT (Concatenate)
EXACT
FIND
LEFT
LEN (Length)
LOWER
MID (Middle)
NUMTOTEXT
PROPER
REPLACE
REPT (Repeat)
RIGHT
TEXTTONUM
TRIM
UPPER

CHAR (Character)

Returns the ASCII character corresponding to the specified value.

Format: =CHAR(number)

Argument:
number: a number, the address of a cell that contains a number, or a field that contains a number. The number can be fractional, but the fraction is discarded.

Example: =CHAR(99.8) returns c, which is represented in ASCII code by 99.

Top

CODE (ASCII Code)

Returns the numeric ASCII code of the first character in the argument.

Format: =CODE(text)

Argument:
text: a text expression

Example: =CODE("z") returns 122, the ASCII code for lowercase z.

Top

CONCAT (Concatenate)

Joins (concatenates) strings of text.

Format: =CONCAT(text1,text2,...)

Argument:
text: a text expression. To specify a literal string of text, place quotation marks around it.

Example: In the spreadsheet below, =CONCAT(A1,", ",B1) returns "Smith, Frank".

Top

EXACT

Returns the logical value TRUE if both arguments are identical in case and characters.

Format: =EXACT(text1,text2)

Argument:
text: any text expression. To specify a literal string of text, place quotation marks around it.

Example: =EXACT(A1,B1) returns FALSE because the two arguments are not identical.

Top

FIND

Searches a text expression for a specified character or series of characters. It starts at the character position specified by "start offset" and returns the starting position of "find text." The search is case-sensitive and spaces are counted.

Format: =FIND(find text,in text,start offset)

Arguments:
find text: the text expression you want to find. Enclose in quotation marks.
in text: the text expression that you want searched. Enclose in quotation marks.
start offset (optional): a numeric expression greater than or equal to 0

Example: =FIND("e","where on earth") returns 3 because "e" is the third character in the phrase "where on earth."

Top

LEFT

Returns the number of characters specified in "number of characters" from the text specified in the text argument. The returned text string starts from the leftmost character in the text argument and extends for the given number of characters.

Format: =LEFT(text,number of characters)

Arguments:
text: a text expression
number of characters: a number, cell reference, or name of field that contains a number

Example: =LEFT("Pauline Mitchell",4) returns "Paul," the first four characters in the text.

Top

LEN (Length)

Counts the number of characters in the text argument, including spaces, numbers, and special characters.

Format: =LEN(text)

Argument:
text: a text expression

Example: =LEN("Cowabunga!") returns 10.

Top

LOWER

Converts uppercase characters in text to lowercase.

Format: =LOWER(text)

Argument:
text: a text expression

Example: =LOWER("Wow, LOOK!") returns "wow, look!"

Top

MID (Middle)

Returns the number of characters specified in "number of characters" from the text specified in the text argument, starting at the position indicated by "start position."

Format: =MID(text,start position,number of characters)

Arguments:
text: a text expression
start position: a numeric expression. If the expression contains a mathematical operation or equation that produces a real number, the fractional part of the number is truncated.
number of characters: a numeric expression

Example: =MID("New Example,",3,6) begins with the third character and counts six characters, and returns "w Exam".

Top

NUMTOTEXT

Converts a number to its textual representation. This function is useful with spreadsheet tasks such as creating charts, where you don't want a number included in a calculation.

Format: =NUMTOTEXT(number)

Argument:
number: a number, address of a cell that contains a number, or name of a field that contains a number

Example: =NUMTOTEXT(1992) returns 1992. AppleWorks interprets this number as a label and won't include it as part of the data series used to build a chart.

Top

PROPER

Capitalizes the first character in each word.

Format: =PROPER(text)

Argument:
text: a text expression

Example: =PROPER("Give peace a chance") returns "Give Peace A Chance."

Top

REPLACE

Replaces "old text" with "new text," starting from the position indicated by "start num" and continuing for the number of characters specified by "num chars."

Format: =REPLACE(old text,start num,num chars,new text)

Arguments:
old text: a text expression
start num: a number indicating the start position in "old text"
num chars: a number indicating the number of characters to replace
new text: a text expression

Example: =REPLACE("my father",4,2,"bro") starts with the fourth character in "my father," replaces text for two characters, and returns "my brother."

Top

REPT (Repeat)

Repeats text the specified number of times.

Format: =REPT(text,# of times)

Arguments:
text: a text expression enclosed in quotation marks
# of times: a numeric expression equal to or greater than 0

Example: =REPT("*",5) returns *****.

Top

RIGHT

Returns a string of text from the characters in the text argument, starting from the end and moving left the number of characters specified.

Format: =RIGHT(text,number of characters)

Arguments:
text: a text expression
number of characters: the number of characters you want to retrieve

Example: =RIGHT("New Example",7) counts seven characters from right to left and returns "Example."

Top

TEXTTONUM

Converts text to a number for functions that require a numeric argument. It retains special characters such as commas, dollar signs, and decimals, except for cases when the characters conflict with numeric standards.

Format: =TEXTTONUM(text)

Argument:
text: a text expression. Enclose in quotation marks.

Example: =TEXTTONUM("456abc78") removes the text that doesn't conform to standard number rules and returns 45678. =TEXTTONUM("365 E. Olive Ave.") returns 365.

Top

TRIM

Removes extra spaces from text.

Format: =TRIM(text)

Argument:
text: a text expression. Enclose in quotation marks.

Example: =TRIM("my       cat      Pancho") returns "my cat Pancho."

Top

UPPER

Converts lowercase text characters to uppercase.

Format: =UPPER(text)

Argument:
text: a text expression. Enclose in quotation marks.

Example: =UPPER("make uppercase") returns "MAKE UPPERCASE."

Top

Related topic

  Alphabetical list of functions

 
Table of contents Index