Section 2, Page 82
Chapter 13
TEXT
Chapter Outline
Concatenate
Paste Special
Text To Columns
Today And Now
Concatenate
“Concatenate” is a powerful “text” function of Excel. “Concatenate” is the function for joining two or
more strings of “data” into a single cell. On the “Concatenate” data file you will find a string of text
entered into the individual cells of column A. In cell B1 the “Concatenate” function is written as:
=CONCATENATE(A1," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",A11," ",A15," ",A16,"
",A17,".")
The result reads:
The quick brown foxes were chased by the slow black dogs.
This function is taking the contents of cell A1, placing a space as shown by the “ ” presentation, then
showing the contents of cell A2 and so on. However, the text string shows that the foxes, no number
specified, were chased by the dogs, no number specified. Since “Concatenate” is classified as a “text”
formula, this would appear to be its limitations. However, as earlier stated, Excel will frequently allow
embedded formulas and “Concatenate” is not the exception to that rule.
In cell B17 several embedded formulas have been added to put numbers into the statement. The
formula now reads:
=CONCATENATE(A1," ",SUM(A2+A4)," ",A5," ",A6," ",A7," ",A8," ",A9," ",
A10," ",A11," ",SUM(A12+A14)," ",A15," ",A16," ",A17,".")
This formula or function results in:
The 2 quick brown foxes were chased by the 4 slow black dogs.
Which includes the number of foxes and dogs as summed by the embedded formulas of “Sum.”
Hint:
Embedded functions and formulas do not get introduced with an “=”.
With this function you can create the mailing label string for invoicing as shown in cells A21 through
E24. In this situation, since the data is intentionally entered horizontally, the “Concatenate” formula was
written into cell E21 and dragged through rows 24.