Chapter 8. Text and Data Functions

• ASC()

• BAHTTEXT()

• CHAR()

• CLEAN()

• CODE()

• CONCATENATE()

• DOLLAR()

• EXACT()

• FIND(), FINDB()

• FIXED()

• LEFT(), LEFTB()

• LEN(), LENB()

• LOWER()

• MID(), MIDB()

• PHONETIC()

• PROPER()

• REPLACE(), REPLACEB()

• REPT()

• RIGHT(), RIGHTB()

• SEARCH(), SEARCHB()

• SUBSTITUTE()

• T()

• TEXT()

• TRIM()

• UPPER()

• VALUE()

This chapter describes the text and data functions in Microsoft Excel (see Table 8-1). The text and data functions provide all the tools you need to convert text or numbers and to edit and evaluate strings. When working with imported lists, you might need these functions so that you can divide, combine, or evaluate text.

Table 8-1. Overview of the Text and Data Functions

Function	Description
ASC()	Converts double-byte characters into single-byte characters
BAHTTEXT()	Converts a number into Thai text using the suffix “Baht”
CHAR()	Returns the character for a code
CLEAN()	Deletes all nonprintable characters from the text
CODE()	Returns the numeric code of the first character in a string
CONCATENATE()	Joins several strings into one string
DOLLAR()	Converts a number into a currency value
EXACT()	Checks whether two strings are exactly the same
FIND(), FINDB()	Finds a string within text
FIXED()	Rounds a number to a specified number of decimal places and returns the value as text
LEFT(), LEFTB()	Returns the number of characters starting at the beginning of the text string
LEN(), LENB()	Returns the number of characters in a string
LOWER()	Converts text into lowercase
MID(), MIDB()	Returns the number of characters starting at a certain position
PHONETIC()	Extracts the phonetic (Furigana) characters from a string
PROPER()	Converts the first letters of all words into uppercase
REPLACE(), REPLACEB()	Replaces a certain number of characters beginning at a given position
REPT()	Repeats a string parameter a given number of times
RIGHT(), RIGHTB()	Returns the number of characters in a text string starting at the end of the string
SEARCH(), SEARCHB()	Finds a string within the text, ignoring uppercase and lowercase distinctions
SUBSTITUTE()	Replaces a certain number of characters in the text
T()	Returns text strings
TEXT()	Converts numeric values into text
TRIM()	Deletes leading and trailing spaces from text
UPPER()	Converts a string into uppercase
VALUE()	Converts text (a number) into a numeric value