chapter 9 creating formulas that manipulate text microsoft office excel 2003
TRANSCRIPT
Chapter 9
Creating Formulas that Manipulate Text
Microsoft Office Excel 2003
Using Text Manipulation Functions
• Excel allows us to manipulate text labels, also called character strings, in worksheets– Fixes information entered by less experienced users – Fixes data imported from different applications.
• Frequently, data from these sources requires “cleaning up” to make it presentable in reports.
• Using Excel’s text functions enable you to – Compare– Convert– Format– Extract– Combine textual data.
Creating Formulas that Manipulate Text
• text = string = text string• Excel distinguishes between numbers and text,
so to have Excel consider numerical data as text you must – Apply the Text Number format to the Cells– Precede the number with an apostrophe (not
displayed)
• Appendix A in book shows listing of text functions in Excel.– Some can be used for non-text values
Character Codes
• Every character has an associated character code. Excel uses the ANSI character set (255 characters numbered 1 to 255) – Alphabetical characters appear in alphabetical order within the
character set.– Lowercase letters follow uppercase letters– Each lowercase letter lies exactly 32 character positions higher
than the corresponding uppercase letter.
• Mathematical operations can be preformed on textual data using the ANSI character set:=A1+1 will increment the value in cell A1 by 1
(if A1 = ‘A’, then the value will be ‘B’)
ISTEXT Function
• ISTEXT()
• Determines if a cell value is text– Single argument (cell address)– Function returns true or false
=ISTEXT(A1) returns true if A1 is text, and returns false if A1 is not character data.
CODE Function
• CODE()• Returns the ANSI character code for a single
character string.– Argument is a character– Returns the character code for the character
passed as the argument.
=CODE(“B”) returns 66, which is the ANSI character code for an uppercase B.
CHAR Function
• CHAR ()• Returns the character for an ANSI character
code.– Argument is a value between 1 and 255– Returns the character for the character code
passed in as the argument.
=CODE(66) returns the letter B (uppercase)
EXACT Function
• EXACT ()• Determines if two strings are identical.
– Two arguments (cell addresses or text strings)– Returns True or False if the strings are the same.– NOT CASE SENSITIVE!
=EXACT(C1, D1) returns True if the strings are the same in each cell.
=EXACT(“HELLO!”, “HELLO? ”) returns false.
Joining Two or More Cells
• Concatenation means joining the contents of two or more cells.
• Excel uses an ampersand (&) as its concatenation operator.– If A1 holds the string “Mr.” and B1 holds the
string “Jones”, we can concatenate these two strings using the formula.
=A1&B1 returns Mr.Jones
• Need to concatenate a space as well:=A1&” “&B1 returns Mr. Jones
Joining Two or More Cells
• To insert a line break character for word wrapping, concatenate the strings using CHAR(10)=A1&CHAR(10)&B1 returns Mr.Jones
• Can also use the CONCATENATE function which takes up to 30 arguments.
TEXT Function
• TEXT()• Displays a value in a specific number formula
– Two arguments (the cell address or text string, the number format)
=“The item costs “ & TEXT(A1,” $#,##0.00”) & “.”
If A1 holds the number 4453.76, this formula will result in:
The item costs $4,453.76.– See Chapter 25 for more number formats.
Concatenating Strings (&, CONCATENATE, TEXT, and TRIM)
Joining cell contents using the CONCA-TENATE function
Concatenating Strings (&, CONCATENATE, TEXT, and TRIM)
Joining cell contents using the & operator
DOLLAR Function
• DOLLAR()• Displays formatted currency values as text.
– Two arguments (the number to convert, and the number of decimal places to display)
– Uses the regional currency symbol.
=“The item costs “ & DOLLAR(A1, 2) & “.”
If A1 holds the number 4453.76, this formula will result in:
The item costs $4,453.76.
REPT Function
• REPT()• Repeats a text string any number of times you
specify.– Two arguments (the text string to repeat, and the number
of times to repeat the string)
=REPT(A1, 2) =REPT(“HA”,2)
this formula will result in HAHA (if the contents of A1 is the text string HA)
• Can use the REPT function to create a histogram, or a frequency distribution chart.
Counting Characters & Removing Excess Spaces and Non-printing Characters
• LEN()– Counts the length of a character string
=LEN(B2) returns 6 if B2 holds the string “hello ”
• TRIM()– Removes all leading and trailing spaces and replaces
internal multiple spaces with a single space.
=TRIM(B2) returns ‘hello’ (without the trailing space)
• CLEAN()– Removes all nonprinting characters from a string
arguments (the text string to repeat, and the number of times to repeat the string)
Changing the Case of Text
• UPPER()– Converts text to all uppercase
=UPPER(B2) returns ‘HELLO’ if B2 holds the string “Hello”
• LOWER()– Converts text to all lowercase.
=LOWER(B2) returns ‘hello’
• PROPER()– Converts text to proper case (first letter of each word is
capitalized)
=PROPER(B2) returns ‘Hello World’ if B2 holds the string “hello world”
Changing the Case (LOWER, PROPER, and UPPER)
Changing the case of character strings
String Extraction Functions
• LEFT()– Returns a specified number of characters from the
beginning of a string
=LEFT(B2, 4) returns ‘Hell’ if B2 holds the string “Hello”
• RIGHT()– Returns a specified number of characters from the
end of a string
=RIGHT(B2, 2) returns ‘lo’ if B2 holds the string “Hello”
String Extraction Functions
• MID()– Returns a specified number of characters beginning
at any position within a string
=MID(B2, 2, 3) returns ‘ell’ if B2 holds the string “hello”
Extracting Characters (LEFT, MID, and RIGHT)
Function Arguments dialog box for the LEFT function
Nesting the SEARCH function in the Num_chars argument text box
Extracting Characters (LEFT, MID, and RIGHT)
Function Arguments dialog box for the RIGHT function
The Num_chars argument text box contains two nested functions
Extracting Characters (LEFT, MID, and RIGHT)
Parsing text labels
String Replacement Functions
• SUBSTITUTE()– Replaces specific text in a string – use when you know
the characters to be replaced, but not the position.
– Three arguments: the string, text to replace, text replacing)
=SUBSTITUTE(“2005 Calendar”, “2005”, “2006”)
– Replaces 2005 with 2006 in the character string 2005 Calendar: results in “2006 Calendar”
String Replacement Functions
• REPLACE()– Replaces text that occurs in a specific location within
a string – use when you know the position of the text to be replaced, but not the actual text.
– Three arguments: the string, the position at which to replace, how many characters to replace, the text you want as the replacement)
=REPLACE(“Mrs.”, 3, 1, “”) returns ‘Mr.’
Finding and Searching within a String
• FIND()– Finds a substring within another text string and
returns the starting position of the substring.– Can specify the character position at which to start
searching.– Use for Case-sensitive comparisons, but no
wildcards allowed.– Arguments: (substring, text string, where to start
searching)
=FIND(“ie”, “retrieve”, 1) returns 5
Finding and Searching within a String
• SEARCH()– Finds a substring within another text string and returns
the starting position of the substring.• Can specify the character position at which to start searching.• Use for non-case-sensitive comparisons or when you want to
use wildcards.• Question Mark (?) matches any single character• Asterisk (*) matches any sequence or characters• Use the tilde (~) for literal matching of ? Or *
– Arguments: (substring, text string, where to start searching)
=SEARCH(“i?”, “retrieve”, 1) returns 5
Analyzing a String (LEN and SEARCH)
Functions Arguments dialog box for the Search function
Argument names appearing in boldface are required entries
Argument names appearing in regular style are optional
Analyzing a String (LEN and SEARCH)
Calculating string lengths and character positions
Searching and Replacing within a String
• Use the REPLACE function with the SEARCH function to replace part of a text string with another string.
– Use the SEARCH function to find the starting location used by the REPLACE function.
=REPLACE(A1, SEARCH(“Profit”, A1), 6, “Loss”)
• Alternatively you can use the SUBSTITUTE function
=SUBSTITUTE(A1, “Profit”, “Loss”)