Excel VBA to Concatenate Cell Values 

While working on Excel, sometimes we need to concatenate cell values. We can use Excel VBA to concatenate cell values easily. For example, if we have a dataset containing Salesperson ID and Salesperson, we can concatenate those values using different methods in Excel VBA. In this article, I’m going to demonstrate to you how we can use Excel VBA to Concatenate Cell Values. To get an overview of how it works, you can watch the following video.

Download Practice Workbook

You can download our practice workbook from here for free!

How to Launch VBA Editor in Excel

As we’ll use the VBA environment throughout this work, we need to enable the VBA setup first if not done so.

Using Module Window:

First of all, open the Developer tab. If you don’t have the developer tab, then you have to enable the developer tab. Then select the Visual Basic command.

Hence, the Visual Basic window will open. Then, we’ll insert a Module from the Insert option where we’ll write the VBA Code.

1. Using Ampersand (&) to Concatenate Cell Values

We have a dataset that contains Salesperson ID and Salesperson. Now we need to concatenate the values in the cells B5 and C5. The following image shows the dataset.

We can use the Ampersand sign (&) in the VBA code to concatenate the cell values. I’ve shown the VBA code in the following image.

Sub concatenateWithAmpersand()
    Dim salespersonID As String
    Dim salesperson As String
    Dim concatenatedWithAmp As String
    salespersonID = Range("B5").Value
    salesperson = Range("C5").Value
    concatenatedWithAmp = salespersonID & " " & salesperson
    Range("D5").Value = concatenatedWithAmp
End Sub

Sub concatenateWithAmpersand()

Dim salespersonID As String
Dim salesperson As String
Dim concatenatedWithAmp As String

salespersonID = Range("B5").Value
salesperson = Range("C5").Value

concatenatedWithAmp = salespersonID & " " & salesperson

Range("D5").Value = concatenatedWithAmp

End Sub

Now if we run the macro, the values in the cells B5 and C5 will be concatenated and stored in the cell D5. The following video contains the demonstration.

You can repeat this step by making necessary adjustments in the code to concatenate the other cell values as well.

If you have thousands of cells will you concatenate each cell manually? Definitely not! VBA offers you loops to iterate through cells and perform the task.

We can use a single macro to automatically concatenate column-wise cell values.

Our dataset contains the Salesperson ID and the Salesperson as the header. The following image shows our dataset.

Now we want to concatenate every Salesperson ID with their corresponding Salesperson. We’ll use VBA to do so.

The following image is the image of the macro that we’ll use to perform the task.

Sub ConcatenateColumnsAutomatically()
    Dim lastRow As Long
    Dim startingRow As Long
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    For startingRow = 5 To lastRow
        Range("D" & startingRow).Value = Range("B" & startingRow).Value & " " & Range("C" & startingRow).Value
    Next startingRow
End Sub

Sub ConcatenateColumnsAutomatically()

Dim lastRow As Long
Dim startingRow As Long

lastRow = Cells(Rows.Count, "B").End(xlUp).Row

For startingRow = 5 To lastRow
Range("D" & startingRow).Value = Range("B" & startingRow).Value & " " & Range("C" & startingRow).Value
Next startingRow

End Sub

If we run the macro now, all the cells in columns B and C will concatenate starting from row 5 to the lastRow. And the concatenated values will be stored in the corresponding cells in column D.

2. Concatenating Cell Values Using Plus (+) Operator

We can also use the Plus operator (+) to concatenate cell values. We’ve got the same dataset as we can see from the following image.

Now, we’ll use the Plus operator (+) instead of the Ampersand sign (&) to concatenate cell values.

The VBA code to do so is as follows.

Sub ConcatenateColumnsWithPlusSign()
    Dim lastRow As Long
    Dim startingRow As Long
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    For startingRow = 5 To lastRow
        Range("D" & startingRow).Value = Range("B" & startingRow).Value + " " + Range("C" & startingRow).Value
    Next startingRow
End Sub

Sub ConcatenateColumnsWithPlusSign()

Dim salespersonID As String
Dim salesperson As String
Dim concatenatedWithPlus As String

salespersonID = Range("B5").Value
salesperson = Range("C5").Value

concatenatedWithPlus = salespersonID + " " + salesperson

Range("D5").Value = concatenatedWithPlus

End Sub

Now, if we run this macro, we can see that the values in cells B5 and C5 are concatenated and stored in cell D5. You can check the following video to observe this.

3. Automatically Concatenating Row Wise Range of Cell Values in Excel VBA

We can concatenate the cell values row-wise too. In this section, I’m going to demonstrate to you two such examples.

3.1 Populating Concatenated Cell Values in A Single Cell

We can concatenate the cell values row-wise and store them in a single cell. The following image represents our dataset.

In this dataset, we’ve Salesperson ID, Salesperson, Product, Product ID, and Total Sales data. Now we want to get a Salesperson ID from the user and concatenate all the data associated with the Salesperson ID and finally store them in a single cell.

We’ll use VBA to do so. The following image contains the code to do so.

Sub ConcatenateSalespersonData()
    Dim salespersonID As Variant
    Dim dataRange As Range
    Dim row As Range
    Dim result As String
    salespersonID = InputBox("Please enter the Salesperson ID:")
    Set dataRange = Range("B:F").Find(What:=salespersonID, LookIn:=xlValues, LookAt:=xlWhole)
    If dataRange Is Nothing Then
        MsgBox "Salesperson ID " & salespersonID & " was not found."
        Exit Sub
    End If
    For Each row In Range(dataRange, Range("F" & Rows.Count).End(xlUp)).Rows
        If row.Cells(1, 1).Value = salespersonID Then
            result = result & " "
                For col = 2 To 6
                    result = result & row.Cells(1, col).Value & " "
                Next col
        End If
    Next row
    Range("G5").Value = salespersonID & " " & Trim(result)
    Range("G5").EntireColumn.AutoFit
End Sub

Sub ConcatenateRowAutomatically()

Dim rng As Range
Dim i As String
Dim SourceRange As Range

Set SourceRange = Range("B5:F5")

For Each rng In SourceRange
i = i & rng & " "
Next rng

Range("G5").Value = Trim(i)

End Sub

If we run the macro now, all the cell values in the range B5:F5 that I’ve specified in the VBA code will be concatenated. And the concatenated value will be stored in cell G5.

The following video demonstrates the process.

3.2 Displaying Concatenated Cell Values in Separate Lines

In this example, we’ll show the concatenated cell values in separate lines in a message box.

The dataset looks like this.

We’ll concatenate the cell values ranging from B5:F5 and display them in a message box. We’ll use VBA to do so.

Our code looks like the following image.

Sub concatenateAndDis()
    Dim salespersonID As String
    Dim salesperson As String
    Dim product As String
    Dim productID As String
    Dim totalSales As Long
    salespersonID = Range("B5").Value
    salesperson = Range("C5").Value
    product = Range("D5").Value
    productID = Range("E5").Value
    totalSales = Range("F5").Value
    MsgBox "Salesperson ID = " & salespersonID & vbCrLf & "Salesperson = " & salesperson & 
    vbCrLf & "Product = " & product & vbCrLf & "Product ID = " & productID & vbCrLf & "Total
    Sales = " & totalSales
End Sub

Sub concatenateAndDis()

Dim salespersonID As String
Dim salesperson As String
Dim product As String
Dim productID As String
Dim totalSales As Long

salespersonID = Range("B5").Value
salesperson = Range("C5").Value
product = Range("D5").Value
productID = Range("E5").Value
totalSales = Range("F5").Value

MsgBox "Salesperson ID = " & salespersonID & vbCrLf & "Salesperson = " & salesperson & vbCrLf & "Product = " & product & vbCrLf & "Product ID = " & productID & vbCrLf & "Total Sales = " & totalSales

End Sub

Now if we run this macro, all the cell values in the range B5:F5 will be concatenated and display the concatenated value in different lines in the message box.

The following video demonstrates the process.

4. Concatenating Column Wise Unique Cell Values

Let’s think about a dataset with repeating values. The following image represents such a dataset.

In this dataset, the Product header has repeating values. Now we want to concatenate the cell values but all unique values just for once.

The following image contains the VBA code to do so.

Sub ConcatUniqueProducts()
    Dim lastRow As Long
    Dim productNames As Object
    Dim cell As Range
    Dim uniqueProductNames As String
    lastRow = Cells(Rows.Count, "D").End(xlUp).row
    Set productNames = CreateObject("Scripting.Dictionary")
    For Each cell In Range("D5:D" & lastRow)
        If Not productNames.exists(cell.Value) Then
            productNames.Add cell.Value, ""
        End If
    Next cell
    For Each Key In productNames.keys
        uniqueProductNames = uniqueProductNames & Key & ", "
    Next Key
    uniqueProductNames = Left(uniqueProductNames, Len(uniqueProductNames) - 2)
    MsgBox "The unique product names are:" & vbCrLf & uniqueProductNames
End Sub

Sub ConcatUniqueProducts()

Dim lastRow As Long
Dim productNames As Object
Dim cell As Range
Dim uniqueProductNames As String

lastRow = Cells(Rows.Count, "D").End(xlUp).Row

Set productNames = CreateObject("Scripting.Dictionary")

For Each cell In Range("D5:D" & lastRow)
If Not productNames.exists(cell.Value) Then
productNames.Add cell.Value, ""
End If
Next cell

For Each Key In productNames.keys
uniqueProductNames = uniqueProductNames & Key & vbCrLf
Next Key

MsgBox "The unique product names are:" & vbCrLf & uniqueProductNames

End Sub

Now if we run the macro, the unique values from column D will be concatenated and displayed in a message box. 

The following video is a demonstration of this. 

Takeaways from This Article

If you’ve followed this article thoroughly, you’ll be now able to:

• Concatenate row-wise cell values.
• Concatenate column-wise cell values.
• Concatenate unique values.

Conclusion

In this article, I’ve demonstrated how we can use Excel VBA to concatenate cell values. I’ve covered examples of concatenating row-wise cell values, column-wise cell values, and unique values. I’ve explained the VBA code as simply as possible so that you can understand them easily. I hope, with a little bit of practice, you can now use this concept of concatenating cell values yourself. If you face any issues regarding this, please let me know in the comment section. The Exceldemy team will be happy to solve those issues. Have a good day!