Know How to Combine Cells in Excel

Do you need to combine Excel cells for your analysis? This blog post explains you to join one or more cells in Excel. Splitting and Concatenating text are basic tasks in Excel.

In the real-time scenario, you used to receive data in multiple fields and have to join them for further analysis.

This article explores Excel’s basic functions and VBA script to concatenate cells in Excel for your report.

How to use Excel & Operator to combine cells in Excel

Suppose you have data in two fields Name & Country and need to combine both. It will be easy to share the file with other users.

Use the formula =A2&”, “&B2 in cell D2 to combine cells A2 and B2 with a comma separator.

Just drag the formula to the cells D2:D11 to apply the same formula.

Excel CONCATENATE function to combine cells in Excel

Paste the formula =CONCATENATE(A2, “, “, B2) in cell F2, Just drag the formula to the cells F2:F11 to apply the same formula for all other cells in the range.

Alternatively, You can use the TEXTJOIN function =TEXTJOIN(“, “,1, A2:B2) in cell H2 to join cells, but it will not work in earlier versions of Excel.

Excel VBA Script to combine cells in Excel

VBA is a programming script to automate your repetitive work. The below script combines the cell data in column A and Column B and Paste it into Column D

Sub combCell()
Dim rg1 As String
Dim rg2 As String

For rNo = 2 To 11
rg1 = ActiveSheet.Cells(rNo, 1).Value
rg2 = ActiveSheet.Cells(rNo, 2).Value
ActiveSheet.Cells(rNo, 4).Value = rg1 & ", " & rg2
Next

End Sub

Copy the above script to your VBE Module and Run.

Steps to run the VBA Macro

Press Alt + F8

Select combCell macro

Press Run.

Conclusions

You have learned to combine cells using Excel function and VBA script.

If you have any questions, please leave a comment below and I will get back to you as soon as possible.