A.N.M. Mohaimen Shanto
Certainly, Excel is a popular and useful tool for arranging and manipulating data. Now, combining multiple cells is a common scenario while working with Excel. However, merging cells with data can lead to some unexpected challenges, so, in this article, we’ll go through the steps on how to merge cells in Excel with data, and solve the issues that arise with its usage.
How to Merge Cells in Excel with Data: 7 Ways
In the meantime, let’s assume the List of Best Sellers dataset shown in the B4:C12 cells. In this situation, the dataset contains the “Book Name” and “Author” columns, and we want to merge the cells with data and return the “Merged Output” in the adjacent column.
Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
1. Using CONCATENATE Function
First of all, let’s merge cells with data using the CONCATENATE function which combines texts together.
📌 Steps:
- Initially, go to the D5 cell >> enter the formula given below >> drag the Fill Handle tool to copy the formula to the cells below.
=CONCATENATE(B5," - ",C5)
Here, the B5 and C5 cells refer to the “Book Name- The Black Swan” and its “Author- N. Taleb”.
Read More: How to Merge Cells in Excel Table
2. Incorporating CONCAT Function
For one thing, the CONCAT function is the successor of the CONCATENATE function and works in a similar manner. In fact, the CONCAT function is available on newer versions of Excel, while the CONCATENATE function can be found in both the older and newer versions.
📌 Steps:
- To begin with, move to the D5 cell >> type in the following expression.
=CONCAT(B5," - ",C5)
For instance, the B5 and C5 cells indicate “The Black Swan” and its “N. Taleb”.
Read More: How to Merge Cells Using Excel Formula
3. Utilizing TEXTJOIN Function
In addition, we can also employ the TEXTJOIN function to join text strings with a delimiter. Henceforth, let’s see it in action.
📌 Steps:
- At the very beginning, insert the equation into the D5 cell as shown in the image below.
=TEXTJOIN(" - ",TRUE,B5,C5)
- TEXTJOIN(” – “,TRUE,B5,C5) → concatenates a range of text string with a delimiter. Here, “-” is the delimiter argument which is the Hyphen character. Next, TRUE is the ignore_empty argument which ignores empty cells. Lastly, the B5 and C5 cell references are the text1 and text2 arguments respectively.
- Output → The Black Swan – N. Taleb
Read More: How to Merge and Center Cells in Excel
Similar Readings
- How to Merge Cells Vertically Without Losing Data in Excel
- How to Merge Multiple Cells without Losing Data in Excel
4. Employing Ampersand Operator
Alternatively, we can merge cells in Excel with the help of the Ampersand (&) operator.
📌 Steps:
- First of all, enter the D5 cell >> insert the formula into the Formula Bar.
=B5&" - "&C5
Read More: VBA to Merge Cells in Excel
5. Applying Flash Fill Option
Besides, Excel has a built-in Flash Fill option that recognizes patterns and auto-fills the adjacent cells. Now, allow us to demonstrate the process in the steps below.
📌 Steps:
- To start with, type in the “Merged Output” for the D5 cell >> hit the CTRL + ENTER keys.
- Next, select the D5:D12 cells >> press the CTRL + E keys.
Boom! That is how simple it is to merge cells in Excel with data.
Moreover, you can observe the steps in real-time in the GIF below.
Read More: How to Merge Multiple Cells in Excel at Once
6. Using Notepad to Merge Cells with Data
Furthermore, the Notepad application can also help us combine cells in Excel, it’s simple and easy, just follow along.
📌 Steps:
- First, highlight the B5:C12 cells >> hit the CTRL + C keys to copy the information into the clipboard.
- Second, launch the Notepad application >> click on the CTRL + V keys to paste the data.
- Third, select the empty space between the “Book Name” and “Author” >> press the CTRL + H keys.
- Following this, insert a “Hyphen” in the Replace with field >> click the Replace All button to replace the blank spaces with a hyphen.
- Fourth, copy the merged data using the CTRL + C keys.
- Finally, paste the information into the D5 cell of the Excel spreadsheet.
Read More: How to Merge Cells in Excel Without Merging Actually
7. Applying VBA Code
Last but not least, we can also apply the VBA code to merge selected cells containing data.
📌 Steps:
- First and foremost, copy and paste the B5:C12 cells into the D5:E12 cells >> navigate to the Developer tab, and launch the Visual Basic editor.
- Not long after, jump to the Insert tab >> select Module.
At this point, copy the code from here and paste it into the window as shown below.
Sub merge_cell_with_data()
Dim value As String
Dim range As range
Set range = Selection
Application.DisplayAlerts = False
For Each Cell In range
value = value & " " & Cell.value
Next Cell
With range
.Merge
.value = Trim(value)
.WrapText = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End Sub
In this case, we’ll explain the VBA code used to merge cells in Excel with data.
- In the first portion, the sub-routine is given a name, here it is merge_cells_with_data().
- Next, define the value and range variables and assign the data type String and Range object respectively.
- Then, use the Set statement to store the selected cells in the range variable and set Application.DisplayAlerts property to False.
- In the second potion, use a For loop to iterate through all the cells in the range and merge their values with the Ampersand operator.
- Lastly, use the With statement to enable Wrap Text and specify text alignment.
- In turn, select the D5:E5 cells >> click the Macros button >> press Run to execute the macro.
Subsequently, this combines the selected cells.
Likewise, repeat the same procedure to merge the other cells as shown in the screenshot below.
Read More: How to Merge Text from Two Cells in Excel
Problems While Using Merge Cells Option
In this situation, let’s glance at the difficulty that arises when using the Merge & Center option in Excel.
Evidently, when we try to merge cells in Excel with data using the Merge & Center button, Excel prompts a warning that it’ll keep only the leftmost value and erase the rest. Now, this is not ideal since this results in the loss of data, therefore, we’ll need to apply the different methods discussed above to rectify this issue.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Download Practice Workbook
Conclusion
To sum up, this tutorial explores all the ins and outs of how to merge cells in Excel with data. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section. Or, you can check out our other articles related to Excel functions on ExcelDemy.
