Page 182 - Excel Workbook for Dummies

18_798452 ch13.qxp 3/13/06 7:53 PM Page 165
Chapter 13: Text Formulas and Functions 165
As you can see in this figure, both the first and last names of each of the clients in the
list are all uppercase letters and need to be converted to the Title case. This is a situ-
ation that you sometimes encounter when using data lists stored in text files that you
import with the Text Import Wizard into an Excel worksheet. (This wizard opens auto-
matically when you try to open a text file with File➪Open.)

Exercise 13-2 shows you how easy it is to take of this type of problem using the
PROPER function. Knowing how to convert text entries to the desired case in a
spreadsheet is very important because it can literally save you from hours of text
editing, not to mention retyping, both of which are a colossal waste of your time.

Try It

Exercise 13-2: Building Formulas with Text Functions that Join Data Entries
and Change Their Case
Open the Exercise13-2.xls workbook file in your Chapter 13 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the
workbook CD-ROM. You will use the uppercase first and last name text entries in
the Client List in its Text Functions worksheet to practice using a couple of Excel’s
Text functions:

1. Position the cell cursor in cell C3 and then click the Insert Function button on
the Formula bar to open the Insert Function dialog box.
2. After selecting Text in the Select a Category drop-down list box, click PROPER in
the Select a Function list box before selecting OK.
3. Click cell A3 in the worksheet to enter its cell reference into the Text Argument
text box in the Function Arguments dialog box and then select OK.
The PROPER function returns Aiken to cell C3.
4. Use the Fill handle to copy this formula with the PROPER function down to the
cell C4:C17 and then over to the cell range D3:D17.
Note that all the first and last name entries are now correct with the exception
of the Mcavoy (which should be McAvoy) in cell C11 and Mcclinton in cell C12
(which should be McClinton). You will have to manually edit these entries later
on as the PROPER function is only able to deal with the first letters in words.
5. Position the cell cursor in cell E3 and then select the CONCATENATE function in
the Insert Function dialog box and select OK.
You will now use the CONCATENATE function to enter the first and last names in
columns C and D as one piece of information in column E.
6. Click cell D3 in the worksheet to enter its cell reference in the Text1 Argument
text box and then press Tab.
In the Text2 Function Argument text box, you must add a blank space; otherwise,
the first and last names are glommed onto each other as a single unit.
7. In the Text2 Argument text box, type “ (a double quotation mark), then press the
spacebar, and then type “ (a double quotation mark) again before you press Tab.
8. Click cell C3 in the worksheet to enter its cell reference in the Text3 Argument
text box.
Check the Formula Result area at the bottom of the Function Arguments dialog
box for the CONCATENATE function. This should now read Christopher Aiken.