CBT Bazar

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.