How to Concatenate Multiple Cells in Excel (7 Easy Ways)

Sometimes, users need to concatenate multiple cells in Excel. Their criteria for concatenating the cells are not always the same. Also, sometimes users have to remember some points before concatenating. If you’re looking for some easy & handy ways to concatenate or combine multiple cells into a single one, then this article should help you the most with a number of basic Excel functions. In this article, I will show you how to concatenate multiple cells in Excel.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

7 Useful Ways to Concatenate Multiple Cells in Excel

To concatenate multiple cells in Excel, there are several options available to meet your requirements. In this article, you will see seven useful methods to concatenate multiple cells in Excel. In the first method, I will use the CONCATENATE function of Excel. Then in the second procedure, I will insert the ampersand operator. Thirdly, I will apply the Merge and Center commands of Excel. Then I will insert line breaks and ASCII codes inside cells as my fourth procedure for doing the same. Fifthly, I will demonstrate the use of the TRANSPOSE function in this regard. Also, you will see the utilization of the TEXTJOIN function as the sixth method. Lastly, I will apply the Fill Justify command to concatenate multiple cells in Excel. I hope these basic functions & formulas will meet all your needs.

To illustrate this article, I will use the following sample data set.

1. Using CONCATENATE Function

Suppose, you have someone’s ID, and his first and last names in a data sheet. You want to combine them into a single cell. How will you do it? To do that, you can use the CONCATENATE function of Excel. By using this function, you can concatenate multiple cells with delimiters like commas or spaces in Excel. For a better understanding, go through the following steps.

Step 1:

• Firstly, select cell E5 in the data set and then write the following formula.

• Here, I will use the function formula to concatenate multiple cells with space.

Step 2:

• Secondly, press Enter to see the result as the cell values from B5, C5 and D5 will be concatenated with space through the formula.
• Then, use AutoFill to drag the formula to the lower cells of the column.

Step 3:

• Thirdly, in order to concatenate multiple cells with a comma, insert the following formula in cell E5.

Step 4:

• Fourthly, after pressing Enter, you will get the desired result.
• Consequently, get the desired result for the whole column by using Fill Handle.

Read More: CONCATENATE vs CONCAT in Excel (2 Ideal Examples)

2. Inserting Ampersand (&) Operator

You can use the Ampersand (&) operator too to concatenate multiple cells. By using ampersands as a formula between two cells, you’re merging these cells together in a cell in Excel. For a better understanding see the following steps.

Step 1:

• First of all, to concatenate multiple cells with ampersand use the following formula in cell E5.

Step 2:

• Secondly, to get the desired result in cell E5 press Enter.
• Then, with the help of AutoFill, fill up the lower cells of the column.

Read More: How to Add Parentheses with CONCATENATE Function in Excel 

3. Applying Merge & Center Command to Concatenate Multiple Cells in Excel

Suppose you have the personal information of a person in a cell. When you select the cell, it’ll show the information is just behind the border between two cells, which looks odd, right? So you have to apply the Merge & Center command here. See the following steps to understand this procedure.

Step 1:

• Firstly, from the following image, you can see that the cell values are not adjusted correctly and that looks odd.
• So, I will merge and center these values in the following steps.

Step 2:

• Secondly, select cell B2:C2 to merge the first header of the data set.
• Then, go to the Home tab of the ribbon, and from the Alignment group, select Merge & Center.

Step 3:

• Thirdly, you will see the cell value of B2 merged and centered in the cell range B2:C2.

Step 4:

• Consequently, select cell range B4:C4 to merge and center the second column header in cell B4 of the data set.
• Then, again go to the Home tab and select Merge & Center.

Step 5:

• Fourthly, the cell value of cell B4 will be merged and centered after the previous step.

Step 6:

• Similarly, follow the previous steps for merging all the cell values one by one.
• Here, remember to merge the cell values one by one instead of choosing them all together at the same time.
• Otherwise, you will see only the first cell value after merging.

Read More: How to Concatenate Multiple Cells with Comma in Excel (4 Ways)

4. Inserting Line Break and ASCII Codes to Concatenate Cells

If you want to add a line break during concatenating multiple cells then you have to insert an ASCII code-named CHAR(10). To learn more about this procedure, see the following steps.

Step 1:

• First of all, select the cell range E5:E14.
• Then, go to the Home tab and select Wrap Text.
• By doing this, after inserting CHAR(10), a line break will appear inside the output.

Step 2:

• Secondly, write the following formula in cell B5.

Step 3:

• Thirdly, after pressing Enter, you will get the desired result with the line break before the full name.
• Consequently, drag the formula to the lower cells using AutoFill.

Read More: How to Concatenate with Space in Excel (3 Suitable Ways)

Similar Readings

• How to Concatenate Email Addresses in Excel (4 Effective Ways)
• How to Bold Text in Concatenate Formula in Excel (2 Methods)
• Carriage Return in Excel Formula to Concatenate (6 Examples)
• How to Concatenate and Keep Currency Format in Excel (3 Methods)
• How to Concatenate Decimal Places in Excel (5 Examples)

5. Using TRANSPOSE Function to Concatenate Multiple Cells in Excel

Additionally, we can use both the CONCATENATE and TRANSPOSE functions together to combine a range of cells from a column. See the following steps for a better understanding.

Step 1:

• First of all, take the following data set where I have divided words of a full sentence into different cells and I want to make a full sentence by joining them.
• In order to do that, insert the following formula in cell B14.

Step 2:

• Secondly, after pressing Enter, you will see all the words in the same row but in different columns.

Step 3:

• Thirdly, to concatenate these words in one cell, use the following formula.

Step 4:

• Fourthly, select the part inside the CONCATENATE function like the following image.
• Then, instead of pressing Enter, press F9.

Step 5:

• Consequently, this process will convert all the cells into text functions at once.
• Then, inside the CONCATENATE function, now you’ll see two types of brackets, remove the curly ones.

Step 6:

• Finally, press Enter & you’ll find the whole range of cells (B4:B11) into a concatenated one.

Read More: Combine CONCATENATE & TRANSPOSE Functions in Excel

6. Utilizing TEXTJOIN Function to Concatenate Multiple Cells

If you’re using MS Office365, then you’ll find this TEXTJOIN function which will meet your requirements more precisely. It will help you to concatenate multiple cells without doing any extra formatting like the previous method. The steps to perform this procedure are as follows.

Step 1:

• First of all, select cell B14 and type the following formula.

• Here, ” ” means you’re adding spaces among all words and TRUE denotes that the function will skip empty cells if found in your range of cells.

Step 2:

• Then, press Enter & you’re done, you’ve just got your desired result.

Read More: How to Concatenate Cells but Keep Text Formatting in Excel

7. Applying Fill Justify Command to Concatenate Multiple Cells in Excel

You can use the Fill Justify command also to combine or concatenate all the cells more quickly. To understand this procedure, follow the following steps.

Step 1:

• Firstly, select cell range B5:B11 for merging.
• Here, increase the column width to adjust the output after merging.
• Then, from the Home tab select Merge & Center.

Step 2:

• Secondly, the command will show you a warning regarding keeping the data only from the first cell after merging.

Step 3:

• In order to merge cells in Excel without losing data, select the desired cell range first.
• Then, from the Home tab select the Fill dropdown in the Editing group.
• Lastly, select Justify from the dropdown.

Step 4:

• Finally, the previous step will merge all the cells into one without losing any data.

Read More: Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to concatenate multiple cells in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.

Related Articles

• How to Add a 1 in Front of Numbers in Excel (7 Easy Ways)
• How to Concatenate Two Columns in Excel (2 Suitable Methods)
• Opposite of Concatenate in Excel (4 Options)
• How to Concatenate Range in Excel (5 Useful Methods)
• How to Combine Row into One Cell in Excel
• Concatenate Not Working in Excel (3 Reasons with Solutions)