Blog‎ > ‎

Excel Text function

posted Jul 11, 2009, 7:52 AM by xsong
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.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.