Page 179 - Excel Workbook for Dummies

18_798452 ch13.qxp 3/13/06 7:53 PM Page 162
162 Part II: Using Formulas and Functions
Excel returns the #NAME? error value because you didn’t enclose the text Summary in
quotation marks. Note that this is necessary even when the text you’re entering is
just a single word with no spaces.

Also, suppose you have the first name Keith entered in cell A3, and the last name
Smith entered next door in cell B3, and you enter the following text formula in cell C3:

=A3&B3

Excel returns to cell C3 the following glommed-together text:

KeithSmith
To have the text formula return the first and last name separated by the customary
space, you need to enter this version of the text formula in cell C3:
=A3&” “&B3

Note that in this version, the invisible-to-the-eye space (entered by pressing the
spacebar) is enclosed in quotation marks, although there are no spaces between
these quotation marks and the & (ampersand) concatenation operators.

Q. When would I typically need to create text person’s title along with his street, city,
formulas in a spreadsheet? state, and ZIP code are all stored in sepa-
rate cells (for purposes of sorting). Text
A. Often you work with data lists where formulas that join these separate pieces of
pieces of information such as the first, information can save hours of retyping
middle, and last name as well as the when you need to reassemble this dis-
parate information in mailing lists.

Try It

Exercise 13-1: Building Simple Text Formulas that Join Data Entries
If Excel is not currently running, launch the program and then open the Exercise
13-1.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 use the entries in the Client Address list in its Text Formulas worksheet to prac-
tice creating simple text formulas:

1. Position the cell cursor in cell G1 and construct a text formula that joins the text
entry Client in cell B1 with the text entry Addresses in cell C1.

Remember that you must add a space enclosed inside quotation marks and sand-
wiched between & (ampersand) operators in the middle of the cell references to
prevent the two text entries from being joined into ClientAddresses in the cell.
2. Copy the formula in cell G1 to the Clipboard (Ctrl+C) and then position the
cell cursor in cell B1. Use the Values option in the Paste Special dialog box
(Edit➪Paste Special) to paste the text and not the formula on top of the Client
entry in cell B1.
Note the repetition of Addresses in G1, which still contains the formula that
combines the text in B1 (Cell Addresses) with the text entry in C1 (Addresses)
so that it now reads Cell Addresses Addresses.
3. Delete the Addresses entry in cell C1.