How to combine text in Excel cells so easily

Suppose that you are working with an Excel sheet where a column of cells contain various text entries. And one of your tasks is to combine the text contents of the cells into one summary cell. How are you gonna do it in Excel? What formulas or functions will you be using?

Using CONCATENATE Function

When working with text in Excel, CONCATENATE function will simply combine the contents of cells into one. The CONCATENATE function joins up to 255 text strings into one text string. The joined items can be text, numbers, cell references, or a combination of those items. For example, if your worksheet contains a person’s first name in cell A2 and the person’s last name in cell B2, you can combine the two values in another cell by using the following formula in C2:

=CONCATENATE(A2," ",B2)

However, if you are to combine several cells with long text entires, the simple method above will still work but will be very tedious.

Using the & Trick

If you are combining two cells containing text, you can use the ampersand (&&) method. For instance, the formula =A2&” “&B2 will produce the same results as the CONCATENATE function. The formula =A2&B2 will simply combine the contents without space, so adding a space requires the second & as shown below:

Combining Multiple Cells with Text

If you are working with several cells with text and you want to combine them, the trick is to use the combination of CONCATENATE and the TRANSPOSE function. Here are the steps:

1. Click on the cell where you want the combined text to appear. For our sample, we will use cell A10 and our text samples are in A1 to A8.
2. Enter the formula =CONCATENATE(TRANSPOSE(A1:A8))
3. DO NOT press Enter yet.
4. Instead, highlight the part TRANSPOSE(A1:A8) in the formula bar as shown below:

5. Then press F9
6. That should convert the TRANSPOSE function into the various text entries in the cells A1 through A8 as shown below:

7. Then remove the curly braces {} in the formula and then press enter.
8. That is it! Cell A10 will now show the combined text.

You can download the sample file below.