| Function |
Syntax |
Description |
Example |
| CHAR |
CHAR(number) |
Returns the character specified by the code
number |
CHAR(65) equals "A" |
| CLEAN |
CLEAN(text) |
Removes
all nonprintable characters from text |
CLEAN(CHAR(7)&"text"&CHAR(7))
equals "text" |
| CODE |
CODE(text) |
Returns
a numeric code for the first character in a text string |
CODE("A")
equals 65
CODE("Alphabet") equals 65 |
| CONCATENATE |
CONCATENATE
(text1,text2,...) |
Joins
several text items into one text item |
CONCATENATE("Total
", "Value") equals "Total Value". This is equivalent
to typing "Total"&"
"&"Value"
Suppose in a stream survey worksheet, C2 contains "species", C5
contains " brook trout", and C8 contains the total 32.
CONCATENATE("Stream population fo |
| DOLLAR |
DOLLAR(number,decimals) |
Converts
a number to text, using currency format |
DOLLAR(1234.567,
2) equals "$1,234.57" |
| EXACT |
EXACT(text1,text2) |
Checks
to see if two text values are identical |
EXACT("word","word")
equals TRUE |
| EXACT("Word","word") equals FALSE |
|
|
|
| FIND |
FIND(find_text,
within_text,start_num) |
Finds
one text value within another (case-sensitive) |
FIND("M","Miriam
McGovern") equals 1
FIND("m","Miriam McGovern") equals 6
FIND("M","Miriam McGovern",3) equals 8 |
| FIXED |
FIXED(number,decimals,
no_commas) |
Formats
a number as text with a fixed number
of decimals |
FIXED(1234.567,
1) equals "1234.6"
FIXED(1234.5
FIXED(-1234.567, -1) equals "-1230"
FIXED(44.332) equals "44.33" |
| LEFT |
LEFT(text,num_chars) |
Returns
the leftmost characters from a text value |
LEFT("Sale
Price", 4) equals "Sale" |
| LEN |
LEN(text) |
Returns
the number of characters in a text string |
LEN("Phoenix,
AZ") equals 11 |
| LOWER |
LOWER(text) |
Converts
text to lowercase |
LOWER("E.
E. Cummings") equals "e. e. cummings" |
| MID |
MID(text,start_num,
num_chars) |
Returns
a specific number of characters from a text string starting at the position
you specify |
MID("Fluid
Flow", 1, 5) equals "Fluid" |
| PROPER |
PROPER(text) |
Capitalizes
the first letter in each word of a text value |
PROPER("this
is a TITLE") equals "This Is A Title" |
| REPLACE |
REPLACE(old_text,
start_num,num_chars, new_text) |
Replaces
characters within text |
REPLACE("1990",
3, 2, "91") equals "1991" |
| REPT |
REPT(text,number_times) |
Repeats
text a given number of times |
REPT("*-",
3) equals "*-*-*-" |
| RIGHT |
RIGHT(text,num_chars) |
Returns
the rightmost characters from a text value |
RIGHT("Sale
Price", 5) equals "Price" |
| SEARCH |
SEARCH(find_text,within_text,start_num) |
Finds
one text value within another (not case-sensitive) |
SEARCH("e","Statements",6)
equals 7 |
| SUBSTITUTE |
SUBSTITUTE(text,old_text,new_text,instance_num) |
Substitutes
new text for old text in a text string |
SUBSTITUTE("Sales
Data", "Sales", "Cost") equals "Cost Data" |
| T |
T(value) |
Converts
its arguments to text |
If
B1 contains the text "Rainfall":
T(B1) equals "Rainfall"
If B2 contains the number 19:
T(B2) equals "" |
| TEXT |
TEXT(value,format_text) |
Formats
a number and converts it to text |
TEXT(2.715,
"$0.00") equals "$2.72"
TEXT("4/15/91", "mmmm dd, yyyy") equals "April 15,
1991" |
| TRIM |
TRIM(text) |
Removes
spaces from text |
TRIM("
First Quarter Earnings
") equals "First Quarter Earnings" |
| UPPER |
UPPER(text) |
Converts text to uppercase |
UPPER("total") equals
"TOTAL"
If E5 contains "yield", then:
UPPER(E5) equals "YIELD" |
| VALUE |
VALUE(text) |
Converts
a text argument to a number |
VALUE("$1,000")
equals 1,000
VALUE("16:48:00")-VALUE("12:00:00") equals
"16:48:00"-"12:00:00" equals 0.2, the serial number
equivalent to 4 hours and 48 minutes. |
