This tutorial explains how to concatenate strings in Excel using VBA code.
You can download the following dataset to practice.
We can concatenate (combine) strings using the ampersand &
operator.
1. Concatenate Two Strings
In the following code, we have used &
operator to combine strings - "My name" and "is Lucifer.".
Sub Concatenate2strings() Dim str1 As String Dim str2 As String Dim result As String str1 = "My name" str2 = "is Lucifer." result = str1 & " " & str2 MsgBox result End Sub
Press Run or F5 to run the above macro.
We use " "
to add a space between two strings.
2. Concatenate Multiple Columns
The following code can be used to concatenate the values stored in columns A and column B and copy the final output to column C :
Sub ConcatenateRangeValues() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row For i = 2 To lastRow ws.Cells(i, 3).Value = ws.Cells(i, 1).Value & " " & ws.Cells(i, 2).Value Next i End Sub
Press Run or F5 to run the above macro.
Share Share Tweet