If you’ve used Excel for any length of time, you know that this spreadsheet application is as adept at manipulating strings of text as it is at manipulating numbers. When it comes to functions, however, Excel users tend to be more familiar with the ones that compute new numbers than those that change text.

If you have two strings of text and want to combine them into one cell, you can use the **Concatenate** function to do just that. Behaving just like any other function in Excel, the **Concatenate** function lets you join strings of text rather than compute numbers.

#### Using Excel’s Concatenate Function

Let’s say you have an Excel worksheet that contains people’s addresses. When you originally entered the addresses, you put the number of the address in one cell and the street on another. Now, you want to combine the number and street into one string.

In the sample spreadsheet pictured above, suppose you want the combined street numbers and street names to appear in the column next to each pair. This is exactly the situation where using the **Concatenate** function is useful.

Begin by clicking on the **C3** cell and then clicking on the **Function** button next to the formula bar. On the **Or Select a Category** drop menu, select **Text** and locate and click on the function titled **Concatenate**. Then, click the **OK** button

Excel will then open up the familiar **Function Arguments** window where you can type in or select a cell or range of cells to include in the current formula. In the **Text1** box, type in **A3** and in the **Text2** box, type in **B3**. Then, click the **OK** button.

You’ll notice that Excel has created a new entry in cell **C3** that is a concatenation of cells **A3** and **B3**. However, there is a problem because the **3** and the **O** in **Oak** are right next to each other. To conform to the standard, you need a space between the numbers and the letters of the address. Rather than put the space in manually, you can force Excel to put a space between concatenated text.

Make sure **C3** is the active cell and go to the function bar. Edit the formula so that is looks like this:

=CONCATENATE(A3,” “,B3)

Notice that there is a space between the open and closed quotation marks. Press the **Enter** key and the address in cell **C3** will now have a space between the number **3** and the **O** in **Oak**.

Now, you can simply drag the square in the box around **C3** down to cell **C9** to concatenate the rest of the addresses in the spreadsheet.

Note that you can concatenate up to 255 cells at one time in Excel; you are not limited to concatenating just two cells at a time. Each time you add a new cell to one of the **Text** boxes in the **Function Arguments** window, another one appears under it. In this way you can concatenate up to 255 cells into one cell. You can even concatenate different types of data such as numbers, text, cell reference, money, dates, etc.

Concatenating multiple cells into one cell in Excel is accomplished using the **Concatenate** function. Using this function, you can join strings of text from up to 255 cells into one and can even add spaces to make the text legible and more usable. This is especially useful when you are compiling a list of addresses and need to add that extra space between the number of the street and the street name itself.

you can do that with the formula in cell C1: =A1 & " " & B1

you can even add text strings like: ="the address is " & A1 & " " & B1

Is there any way to do the reverse of concatenation? I have a cell with an address and city and I want to put the city and address into different cells.

I need to take the resulting text string and make it a text value so I can copy and paste it. Of course, the concatenate formula is in the cell even though the joined text is displayed. How can I grab the resultant combined address string and put it in another cell as plain text that I can copy and paste? I’m searching for this answer, but I’m not sure how to phrase the search term.

Whoops! I found my answer. I must change my formulas to values. Instructions are here: http://www.mrexcel.com/tip074.shtml

This might be a good adendum to your article.