Page 180 - Excel Workbook for Dummies

18_798452 ch13.qxp 3/13/06 7:53 PM Page 163
Chapter 13: Text Formulas and Functions 163
Note that both B1 and G1 now show Client Addresses. In cell B1, this is the result
of a text entry created from a copy of the original text formula; whereas in G1, it
is the result of the original text formula you constructed.
4. Position the cell cursor in cell G3 and construct a new text formula that joins the
house number in cell A3 with the street name in B3.
The text formula you enter in cell G3 should appear on the Formula bar as
=A3&” “&B3
Note that the entry in cell A3 is the number 123 (not entered as a text with a
preceding quotation mark nor formatted with the Text format), yet Excel has
no problem joining this value to the obvious text entry in cell B3 using the
concatenation operator.
5. Copy the text formula in cell G3 down to the cell range G4:G17 and then use
AutoFit to widen column G as needed to display all the conjoined street
addresses.
6. Position the cell cursor in cell H3 and construct a text formula that combines the
city name in cell C3 with the state abbreviation in cell D3 and the ZIP code in cell
E3, making sure that there is a comma and a space immediately following the city
and spaces between the state and ZIP code.
The result in H3 should appear as
Centerville, IL 60789
Don’t forget to enclose the comma and the trailing space after the reference to
cell C3 with the city in quotation marks, as in
=C3&”, “&
7. Copy the text formula in cell H3 down to the cell range H4:H17 and then use
AutoFit to widen columns as needed to display all the conjoined city, state, and
ZIP code information.
8. Position the cell cursor in cell I3 and construct a formula that joins the text in
cell G3 with that in cell H3. Make sure that this formula inserts a comma and a
trailing space between the street address in cell G3 and the city, state, and ZIP
in cell H3.
9. Copy the text formula in cell I3 down to the cell range I4:I17 and then use AutoFit
to widen column I to suit (you may also have to scroll the screen to the right to
display all the data).
10. While the cell range I3:I17 still selected, click the Sort Ascending button on the
Standard toolbar (the one with the A over Z and the arrow pointing downward).
If a Sort Warning dialog box appears, select the Continue with the Current
Selection option button before you select its Sort button. (Excel is concerned
that you forgot to include data that needs sorting in the cell range G3:H17.)
Note that nothing changes in the order of the addresses in the selected range
when you finish the Sort operation. This is because the cells contain text formu-
las rather than the actual text entries you see displayed. If you want to be able to
find and sort text entries that you create in a spreadsheet with text formulas,
you need to replace the formulas with their values. In this particular case, you
copy the value on top of the formulas, thereby replacing them.
11. While the cell range I3:I17 still selected, click the Copy button on the Standard
toolbar or press Ctrl+C and then (without doing a thing to the cell selection)
open the Paste Special dialog box (Edit➪Paste Special). Select the Values option
button in the Paste section before you select OK.
Excel replaces the formulas with their calculated values in the same range (you
can verify this because the contents of the Formula bar now reads 123 Niles
Avenue, Centerville, IL 60789 instead of =G3&", "&H3).