MSOfficeExcel 2007 Tutorial Text Functions 83 codes

1 =CLEAN(CHAR(7)&text&CHAR(7))

2 =CODE(java2s)

3 =DOLLAR(A2, -2)

4 =DOLLAR(A5)

5 =EXACT(zero ,zero) returns FALSE because the first string contains a trailing space

6 =FIND(m,A2)

7 =FIND(M,A2,3)

8 =FIXED(-1234 567, -1, TRUE)

9 =FIXED(44 332)

10 =FIXED(A2, -1)

11 =IF(LEFT(A1,1)= ,SUBSTITUTE(A1, ,,1), A1)

12 =LEFT(A3)

13 =LENB(ABC)

14 =MIDB(ABCasdf,4,2)

15 =REPLACE(A3,3,2,10)

16 =RIGHT(A3)

17 =SEARCH (bd,A1)

18 =SEARCH (Birthday, A1)

19 =SEARCH (y,A1) returns the first place where a y is followed by any two characters

20 =SEARCH (yo,A1) returns the first place where yo is followed by any single character

21 =SUBSTITUTE(a b c d, ,,,1)

22 =SUBSTITUTE(a b c d, ,,,3)

23 =SUBSTITUTE(a b c f, ,,)

24 =SUBSTITUTE(A3, 1, 2, 1)

25 =SUBSTITUTE(two two ,two, three)

26 =SUBSTITUTE(two two ,two, three,2)

27 =TEXT(0 4,#%)

28 =TEXT(1234 56,# #) (1)

29 =TEXT(1234 56,$#) (3)

30 =TEXT(3152005,mmddyy)

31 =TEXT(3152005,mmddyyyy)

32 =TEXT(3152005,mmm-dd)

33 A case-insensitive comparison

34 ASC changes full-width (double-byte) English letters to half-width (single-byte) characters

35 BAHTTEXT(number) converts a number to text, using the baht currency format

36 CHAR(number) returns the character specified by the code number

37 CLEAN removes all nonprintable characters from text

38 CODE(text) returns a numeric code for the first character in a text string

39 CONCATENATE (text1,text2, ) joins several text items into one text item

40 Count the number of Bs (both upperand lowercase) in the string

41 Count the number of Bs (uppercase only) in the string

42 Display asterisk padding on both sides of the number

43 Display text such as Today is Friday, September 14, 2007

44 Display the value along with the asterisk padding on the left

45 Displaying Formatted Currency Values as Text

46 DOLLAR converts a number to text, using the $ (dollar) currency format

47 EXACT(text1,text2) checks to see if two text values are identical

48 Extracting the First Word of a String

49 Extracting the First Word of a String with ISERR

50 FIND(find_text,within_text,start_num) finds one text value within another (case-sensitive) and return the index

51 FIXED(number,decimals,no_commas) formats a number as text with a fixed number of decimals

52 Get count of characters where the space is from left

53 If cell A1 contains the letter A (uppercase) The following formula returns the letter a (lowercase)

54 If the argument for CODE consists of more than one character, the function uses only the first character

55 LEFT(text,num_chars) returns the leftmost characters from a text value

56 LEN(text), LENB(text) returns the number of characters in a text

57 LOWER(text) converts text to lowercase

58 Pad the number with asterisks on the left

59 Pad the number with dashes

60 PHONETIC(reference) extracts the phonetic (furigana) characters from a text string

61 PROPER(text) capitalizes the first letter in each word of a text value

62 REPLACEB(old_text,start_num,num_bytes,new_text) replaces characters within text

63 REPT(text,number_times) repeats text a given number of times

64 Return the number of occurrences of a substring in B1 within a string in A1

65 Returns the first nine characters plus an ellipsis for longer string

66 Returns the number of words

67 RIGHT(text,num_chars), RIGHTB(text,num_bytes) returns the rightmost characters from a text value

68 Search for the word Report and replaces those six characters it with the word Summary

69 Sentence case

70 Strips space, hyphen, colon, asterisk, underscore, left parenthesis, and right parenthesis

71 SUBSTITUTE(text,old_text,new_text,instance_num) substitutes new text for old text in a text string

72 TEXT(A1,$#,##0 00)

73 TEXT(AVERAGE(A

74 TEXT(value,format_text) formats a number and converts it to text

75 To demonstrate the opposing nature of the CODE and CHAR functions

76 To pad the number with asterisks, use two asterisks

77 TRIM(text) removes spaces from text

78 UPPER(text) converts text to uppercase

79 Use the REPLACE function to replace one character beginning at position 5 with nothing

80 Use the SUBSTITUTE function to remove all spaces from a string

81 Uses the NOW and TEXT function to format the date and time

82 Using the CHAR function to represent a comma (44) and a space (32) between the two entries

83 VALUE(text) converts a text argument to a number