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.
- 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.
- 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.
- Thirdly, in order to concatenate multiple cells with a comma, insert the following formula in cell E5.
=CONCATENATE(B5, ", ", C5, ", ", D5)
- 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
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.
- First of all, to concatenate multiple cells with ampersand use the following formula in cell E5.
=B5& " " &C5& " " &D5
- 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.
- 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.
- 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.
- Thirdly, you will see the cell value of B2 merged and centered in the cell range B2:C2.
- 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.
- Fourthly, the cell value of cell B4 will be merged and centered after the previous step.
- 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
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.
- 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.
- Secondly, write the following formula in cell B5.
=B5& CHAR(10) &C5& " " &D5
- 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.
- 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.
- Secondly, after pressing Enter, you will see all the words in the same row but in different columns.
- Thirdly, to concatenate these words in one cell, use the following formula.
=CONCATENATE(TRANSPOSE(B5:B11) & “ “)
- Fourthly, select the part inside the CONCATENATE function like the following image.
- Then, instead of pressing Enter, press F9.
- 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.
- Finally, press Enter & you’ll find the whole range of cells (B4:B11) into a concatenated one.
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.
- 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.
- 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.
- 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.
- Secondly, the command will show you a warning regarding keeping the data only from the first cell after merging.
- 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.
- Finally, the previous step will merge all the cells into one without losing any data.
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.