Excel 4 Ur Business

Overview

• I have a spreadsheet 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 region sales. How can I extract East, North, and South sales to separate cells?
• I download quarterly gross national product (GNP) data from the Web. The cell containing first quarter data for 1980 contains the entry 1980.1 5028.8. How can I place the date and GNP value in different cells?

• In the spreadsheet I use for a mailing list, column A contains people’s names, column B contains their street address, and column C contains their city and zip code. How can I create each person’s full address in column E?

When someone sends you data or you download data from the Web, often the data 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 need them to be in separate cells. How can you manipulate data so that it has the format you need? The answer is to become good at using Excel’s set of text functions. In this chapter, I’ll show you how to use the following

Excel text functions to magically manipulate your data until it looks the way you want:

• LEFT
• RIGHT
• MID
• TRIM
• LEN
• FIND
• SEARCH
• CONCATENATE
• REPLACE
• VALUE

Text Function Syntax

The file Reggie.xls, shown in the following figure, includes examples of text functions. You’ll see how to apply these functions to a specific problem later in the chapter, but let’s begin by describing what each of the text functions do. Then we’ll combine these functions to perform some fairly complex manipulations of data.

The LEFT Function

The function LEFT(text,k) returns the first k characters in a text string. For example, cell C3 contains the formula LEFT(A3,4). Excel returns Regg.

The RIGHT Function

The function RIGHT(text,k) returns the last k characters in a text string. For example, in cell C4, the formula RIGHT(A3,4) returns ller.

The MID Function

The function MID(text, k, m) begins at character k of a text string and returns the next m characters. For example, the formula MID(A3,2,5) in cell C8 returns characters 2-6 from cell A3, the result being eggie.

The TRIM Function

The function TRIM(text) removes all spaces from a text string except for single spaces between words. For example, in cell C5 the formula TRIM(A3) eliminates two of the three spaces between Reggie and Miller and yields Reggie Miller.

The LEN Function

The function LEN(text) returns the number of characters in a text string (spaces are included). For example, in cell C6 the formula LEN(A3) returns 15 because cell A3 contains 15 characters. In cell C7, the formula LEN(C5) returns 13. Because the

Explore posts in the same categories: MS Excel Tune Up