1 / 30

Chapter 9

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

orinda
Download Presentation

Chapter 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 Creating Formulas that Manipulate Text Microsoft Office Excel 2003

  2. 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.

  3. 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

  4. 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’)

  5. 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.

  6. 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.

  7. 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)

  8. 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.

  9. 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

  10. 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.

  11. 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.

  12. Concatenating Strings (&, CONCATENATE, TEXT, and TRIM) Joining cell contents using the CONCA-TENATE function

  13. Concatenating Strings (&, CONCATENATE, TEXT, and TRIM) Joining cell contents using the & operator

  14. 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.

  15. 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.

  16. 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)

  17. 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”

  18. Changing the Case (LOWER, PROPER, and UPPER) Changing the case of character strings

  19. 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”

  20. 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”

  21. 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

  22. Extracting Characters (LEFT, MID, and RIGHT) Function Arguments dialog box for the RIGHT function The Num_chars argument text box contains two nested functions

  23. Extracting Characters (LEFT, MID, and RIGHT) Parsing text labels

  24. 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”

  25. 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.’

  26. 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

  27. 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

  28. 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

  29. Analyzing a String (LEN and SEARCH) Calculating string lengths and character positions

  30. 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”)

More Related