I love print books. I like the way they feel and I like how I can picture pages in my head after I’ve read them. I like sticking post-it notes on pages that might be important to me again. I already stare at a screen all day reading email. Why would I want to read books on a screen – no matter if the screen is my monitor or an electronic reader? Yet, over the last few months, I’ve found ways to use PDFs and online libraries that make me rethink digital books – especially technical books like the ones we publish at Microsoft Press.

I’m lucky enough to have a bookcase full of our books in my office. Sometimes I’ll take one home to learn something new, like SharePoint. However, if I have a specific question and don’t know which book can solve it best, I rely on searching through our entire digital library.

Just this past week, I had an idea for a worksheet that required me to combine a bunch of Excel cells into one cell. My first instinct was to use the merge feature, but merging cells messed up my spreadsheet. My next idea was to find a function that would solve my problem. I typed the words “combine cells in Excel” into our search field in our online library. A list of our books appeared with links to the specific pages that contained my search words. Bingo! “CONCATENATE” fit the bill. What follows is a sampling of what I found. What’s great about finding information this way is that if one result doesn’t answer my question or make it clear enough, most likely, some other result will.

9780735623965f From Chapter 5 -Text Functions, Microsoft Office Excel 2007: Data Analysis and Business Modeling by Wayne Winston, ISBN 9780735623965:

The CONCATENATE and & Functions

The function CONCATENATE(text1,text2, …,text30) can be used to join up to 30 text strings into a single string. The & operator can be used instead of CONCATENATE. For example, entering in cell C12 the formula A1&""&B1 returns Reggie Miller. Entering in cell D12 the formula CONCATENATE(A1," ",B1) yields the same result.

9780735623217f From Chapter 14 – Everyday Functions, Microsoft Office Excel 2007 Inside Out by Mark Dodge and Craig Stinson, ISBN:9780735623217:

The CONCATENATE Function

To assemble strings from up to 255 smaller strings or references, the CONCATENATE function is the function equivalent of the & character. For example, if cell B4 contains the text Pacific with a trailing space character, the formula =CONCATENATE(B4, "Musical Instruments") returns Pacific Musical Instruments.

TROUBLESHOOTING

Concatenated dates become serial numbers.

If you try to concatenate the contents of a cell formatted as a date, the result is probably not what you expect. Because a date in Excel is only a serial number, what you usually see is a formatted representation of the date. But when you concatenate the contents of a date-formatted cell, you get the unformatted version of the date. To avoid this problem, use the TEXT function to convert the serial number to a recognizable form. For example, suppose cell A1 contains the text Today's Date is and cell A2 contains the function = NOW() and is formatted to display the date in dd/mm/yyyy format. Nonetheless, the formula =CONCATENATE(A1, " ", A2) results in the value Today's Date is 39511 (or whatever the current date serial number happens to be). To remedy this problem, type the TEXT function as follows: =CONCATENATE(A1, " ", TEXT(A2, "dd/mm/yyyy")).

This version returns the value Today's Date is 03/04/2008 (or whatever today's date happens to be). Note that the formula includes a space character as a separate argument (" ") between the two cell reference arguments.

From Chapter 12 – Building Formulas

You can use the & (ampersand) operator to concatenate, or join, several text values. Extending the preceding example, if cell A2 contains the text Quarter and you type the formula =A1&A2 in cell A3, then cell A3 displays FirstQuarter. To include a space between the two strings, change the formula to =A1&" "&A2. This formula uses two concatenation operators and a literal string, or string constant (a space enclosed in quotation marks).

You can use the & operator to concatenate strings of numeric values as well. For example, if cell A3 contains the numeric value 867 and cell A4 contains the numeric value 5309, the formula =A3&A4 produces the string 8675309. This string is left-aligned in the cell because it's considered a text value. (Remember, you can use numeric text values to perform any mathematical operation as long as the numeric string contains only the numeric characters listed at the top of this page.)

Finally, you can use the & operator to concatenate a text value and a numeric value. For example, if cell A1 contains the text January and cell A3 contains the numeric value 2009, the formula =A1&A3 produces the string January2009.

Image from book

Practical Concatenation

 

Depending on the kind of work you do, the text manipulation prowess of Excel may turn out to be the most important skill you learn in this book. If you deal with a lot of mailing lists, for example, you probably use a word-processing application such as Microsoft Office Word 2007. But read on—you might find that Excel has the tools you've been wishing for, and it just might become your text manipulation application of choice.

Suppose you have a database of names in which the first and last names are stored in separate columns. This example shows you how to generate a list of full names:

Image from book

We created the full names listed in columns D and E using formulas like the one visible in the formula bar. For example, the formula in cell D2 is =A2&" "&B2, which concatenates the contents of the cells in columns A and B and adds a space character in between. The formula in cell E2 (shown in the illustration) reverses the position of the first and last names and adds a comma before the space character.

Pretty soon, you'll be using the term concatenate in everyday conversation. Instead of the old "ducks in a row" metaphor, you'll be saying, "We must concatenate our ducks." Caution is advised.

Image from book

On the CD You'll find the Image from book Concatenation.xlsx file in the Sample Files section of the companion CD.