# 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.

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 to Concatenate Multiple Cells in Excel

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.
`=CONCATENATE(B5, " ", C5, " ", D5)`
• 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.
`=CONCATENATE(B5, ", ", C5, ", ", D5)`

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.

## 2. Inserting Ampersand (&) Operator to Concatenate Multiple Cells in Excel

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.
`=B5& " " &C5& " " &D5`

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.

## 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.

## 4. Inserting Line Break and ASCII Codes to Concatenate Cells in Excel

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.
`=B5& CHAR(10) &C5& " " &D5`

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.

## 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.
`=TRANSPOSE(B5:B11)`

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.
`=CONCATENATE(TRANSPOSE(B5:B11) & â€œ â€œ)`

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.

## 6. Inserting 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.
`=TEXTJOIN(" ", TRUE,B5:B11)`
• 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.

## 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.

## Excel Concatenate Multiple Cells: Knowledge Hub

<< Go Back To Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!