Use Excel’s Concatenate Function to Join Several Strings of Text

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.

Sample Address Excel Spreadsheet

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

Select Text and Then Concatenate

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.

Excel Function Arguments Window

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.

Put a Space Between Concatenated Text

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.

Drag the Excel Function Down

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.

Concatenate More Than Two Cells in Excel

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.

More posts from the Office Tips Category

Make OpenOffice Writer Look and Function More Like Microsoft Word

Embed an Excel Worksheet into Word 2007

Example of a line of text with small bullets

Insert a Bullet in the Middle of a Sentence in Word

Fix Office 2010 “Cannot Verify License” Error Message

Popular Posts This Month

event id

Fix Windows 8 App Opens Then Closes

runas command

5 Windows Alternatives to the Linux sudo Command

5 Utilities for Changing DNS Servers in Windows Reviewed

windows defender

Windows Defender vs. Security Essentials vs Safety Scanner

Comments [4]

  1. dingoe says:

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

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

  2. William says:

    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.

  3. Kimberley says:

    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.

  4. Kimberley says:

    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.

Leave a Reply