How to Merge Cells in Excel with Data (7 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for how to merge cells in excel with data

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

how to merge cells in excel with data Using CONCATENATE Function

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

how to merge cells in excel with data using CONCAT Function

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)

Formula Breakdown
  • 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

how to merge cells in excel with data Utilizing TEXTJOIN Function

Read More: How to Merge and Center Cells in Excel


Similar Readings


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

how to merge cells in excel with data Employing Ampersand Operator

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.

Applying Flash Fill Option

  • Next, select the D5:D12 cells >> press the CTRL + E keys.

Using keyboard shortcut to flash fill

Boom! That is how simple it is to merge cells in Excel with data.

how to merge cells in excel with data with flash fill option

Moreover, you can observe the steps in real-time in the GIF below.

GIF showing steps for flash fill to merge cells with data in excel

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.

Using Notepad to Merge Cells with Data

  • Second, launch the Notepad application >> click on the CTRL + V keys to paste the data.

Using keyboard shortcut to paste values

  • Third, select the empty space between the “Book Name” and “Author” >> press the CTRL + H keys.

Using find and replace option

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

how to merge cells in excel with data with Notepad application

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.

Applying VBA Code

  • Not long after, jump to the Insert tab >> select Module.

Inserting 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

VBA code

Code Breakdown

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.

VBA code explanation

  • In turn, select the D5:E5 cells >> click the Macros button >> press Run to execute the macro.

Running macro

Subsequently, this combines the selected cells.

Merging selected cells with data

Likewise, repeat the same procedure to merge the other cells as shown in the screenshot below.

how to merge cells in excel with data applying VBA Code

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.

Problems While Using Merge Cells Option


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.

Practice Section


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.


Related Articles

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo