Chapter 5: Text Functions

Overview

• I have a worksheet in which each cell contains a product description, a product ID, and a product price. How can I put all the product descriptions in column A, all the product IDs in column B, and all the prices in column C?

• Every day, I receive data about total U.S. sales, which is computed in a cell as the sum of East, North, and South regional sales. How can I extract East, North, and South sales to separate cells?

• At the end of each school semester, my students evaluate my teaching performance on a scale from 1 to 7. I know how many students gave me each possible rating score. How can I easily create a bar graph of my teaching evaluation scores?

When someone sends you data or you download data from the Web, it often isn’t formatted the way you want. For example, when downloading sales data, dates and sales amounts might be in the same cell, but you might need them to be in separate cells. How can you manipulate data so that it appears in the format you need? The answer is to become good at using the Microsoft Office Excel 2007 text functions. In this chapter, I’ll show you how to use the following Excel text functions to magically manipulate your data so it looks the way you want:

• LEFT

• RIGHT

• MID

• TRIM

• LEN

• FIND

• SEARCH

• REPT

• CONCATENATE

• REPLACE

• VALUE

• UPPER

• LOWER

• CHAR