In this article we Concatenate several cells into one cell with VBA code in Excel. How do I convert a range of cells with a VBA macro in Excel 2013/2016. How to merge the contents of two or several cells in a cell with a macro in Excel.

Concatenate multiple Cells into One Cell with VBA

Merge several cells into one cell without losing data in Excel, you can try to use Excel VBA macro to get results. Just do the following steps:

Step 1

Open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step 2

click “Insert” ->”Module” to create a new module

Step 3

Copy and paste the below VBA code into the code window. Then clicking “Save” button.

Sub MergeCells()
    Dim xJoinRange As Range
    Dim xDestination As Range

    Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)
    Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
    temp = ""
    For Each Rng In xJoinRange
        temp = temp & Rng.Value & " "
    Next
    xDestination.Value = temp
End Sub

Step 4

Come back to the current worksheet, then run the above excel macro. Click Run button.

Step 5

Now select one range of cells to merge cells

Step 6

Then select one destination cell

Step 7

Now result appear .

See the image to understand simply

Merge multiple Cells into One Cell with VBA

And create a date in Excel from 3 different cells. How can i combine 3 normal cells into one date cell using excel formula.

Concatenate 3 Cells to Generate Date in Excel

If you want to combine 3 different cells to generate one standard data, you can create an Excel formula based on the DATE function. Let’s look at the generic formula below:

= DATE(year, month, day)

Example,

Step 1 :

Suppose your year value is in cell A2, the month value is in B2, and the day value is in cell C2. And then you can write the following formula using the DATE function:

=DATE(A2,B2,C2)

Enter this formula into Cell D2, then press Enter.

Step 2 :

Use the concatenate operator to combine three cells to one date cell, so you can use “&” to create the below formula:

See the image to understand simply

=A1&”-“&B1&”-“&C1

Enter the formula into cell D2, then press Enter.

Concatenate 3 Cells to Generate Date in Excel