How to Use Concatenate in Excel VBA (4 Methods)

In Excel, concatenation is the process of joining two strings together to form a single string. Simply said, if we have a table with first names in one column and last names in another, we can use the concatenation procedure to concatenate and combine them in a single cell in a split second. In Excel, we have a function called CONCATENATE () that allows us to do this concatenation. However, in VBA, this type of function is not permitted. We can’t use CONCATENATE () in the VBA code since it won’t work. Because VBA lacks built-in functions and we can’t utilize spreadsheet functions. So, this lesson will demonstrate to you how to use VBA concatenate to combine multiple cells, columns, and rows in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the VBA Concatenate Function

As we have mentioned that Excel has not any built-in function for the VBA Concatenate, but we can make it works as a function by combining different strings with operators. Here we use the ampersand (&) as our operator.

⟴ Syntax

String1 = “First Text”

String2 = “Second Text”

⟴ Return Value

Return_value = String1 & String2


4 Different Uses of VBA Concatenate Function in Excel

Here, we will use 4 different approaches to perform the concatenate process. We will apply different operators in combination with VBA code to achieve this.

1. Use Ampersand (&) Operator to Join Cells in VBA Concatenate

As shown in the below screenshot, we have a two-column data collection with first names in one column and last names in the other. By merging the two columns, we can now get the whole names. Because VBA doesn’t have any built-in methods for concatenation, we’ll use the ampersand (&) operator as detailed in the instructions below.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Step 1:

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Step 2:

  • To combine two cells into one, copy and paste the following VBA
Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 & String2
MsgBox (full_string)
End Sub

Here,

  • String1 = Cells(5, 2).Value is the first cell location B5, row 5, and column 2.
  • String2 = Cells(5, 3).Value is the second cell location C5, row 5, and column 3.
  • Cells(5, 5).Value = String1 & String2 is the result cell location E5, row 5 and column 5.
  • String1 & String2 are the two strings joined by the ampersand (&)

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Step 3:

  • Save and press F5 to run the program.

Therefore, you will get the result in the E5 cell of your current worksheet.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Step 4:

  • Follow and repeat the steps for the rest cells and get the results as shown in the below image.

Use Ampersand (&) Operator to Join Cells in VBA Concatenate

Note. To run VBA code, every time make sure your Excel file is saved in Excel Macro-Enabled Worksheet (xlsm.) format.

Read More: How to Use VBA StrComp in Excel (5 Common Examples)


2. Use Plus (+) Operator to Join Cells in VBA Concatenate

As described in the previous section, we have used the ampersand (&) operator to join cells strings. You can obtain the same result by applying the plus (+) sign in lieu of the ampersand (&) operator. To have it done follow these steps.

Use Plus (+) Operator to Join Cells in VBA Concatenate

Step 1:

  • To open Macro in Excel, press Alt + F11.
  • Click Insert and select
  • After opening the program page, paste the following VBA
Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(5, 2).Value
String2 = Cells(5, 3).Value
Cells(5, 5).Value = String1 + String2
MsgBox (full_string)
End Sub

Here,

  • Cells(5, 5).Value = String1 + String2 is this line we use plus (+) sign instead of the ampersand (&)

Use Plus (+) Operator to Join Cells in VBA Concatenate

Step 2:

  • After pasting, save and press F5 to run the program. Consequently, you will see the change in cell E5.

Use Plus (+) Operator to Join Cells in VBA Concatenate

  • To obtain the final results, fill up the required cells by performing the previous steps again.

Use Plus (+) Operator to Join Cells in VBA Concatenate

Read More: How to Use VBA StrConv Function (5 Examples)


Similar Readings:


3. Add Multiple Columns Using VBA Concatenate

In the previous two approaches, we discussed how to combine two cells. However, if we want to apply it to the entire column, adding one by one will take a long time. We’ll teach you how to add multiple columns totally with VBA code for this.

Add Multiple Columns Using VBA Concatenate

Step 1:

  • Firstly, to open Macro press Alt + F11
  • Choose Module from the Insert tab
  • Then, paste the following VBA
Sub ConcatCols()
'concatenate columns B & C in column E
Dim LastRow As Long
With Worksheets("Sheet3")
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("E5:E" & LastRow)
.Formula = "=B5&C5"
.Value = .Value
End With
End With
End Sub

Here,

  • With Worksheets(“Sheet3”) is your current worksheet name.
  • LastRow = .Cells(.Rows.Count, “B”).End(xlUp).Row is the first column name.
  • With .Range(“E5:E” & LastRow) is the result return cell range.
  • .Formula = “=B5&C5” is the formula to join the first cell of the range.

 Add Multiple Columns

Step 2:

  • Then, finally, save and press F5 to run the program.

As a result, you will obtain the results entirely in a column.

 Add Multiple Columns

Read More: How to Use VBA Rnd in Excel (4 Methods)


4. Join Multiple Rows Using VBA Concatenate

In addition to adding multiple columns, we can also apply VBA code to concatenate multiple rows into one. As shown in the below screenshot, we want to concatenate the three rows into one. To concatenate the rows, follow the simple steps below.

Join Multiple RowsStep 1:

  • For activating Macro in Excel, press Alt + F11.
  • Then, select Module from the Insert
  • To concatenate the rows, paste the VBA
Sub vba_concatenate()
Dim rng As Range
Dim i As String
Dim SourceRange As Range
Set SourceRange = Range("B5:D5")
For Each rng In SourceRange
i = i & rng & " "
Next rng
Range("B8").Value = Trim(i)
End Sub

Here,

  • Set SourceRange = Range(“B5:D5”) is the source cell range.
  • Range(“B8”).Value = Trim(i) is the return cell number.

Join Multiple Rows

Step 2:

  • Finally, save the program and press F5 to run.

Thus, the final result concatenating the three rows will be shown in cell B8.

Join Multiple Rows

Read More: How to Unhide Top Rows in Excel (7 Methods)


Conclusion

To summarize, I hope that this post has provided clear instructions on how to utilize VBA concatenate in Excel in a variety of ways. All of these techniques should be learned and used on your data. Examine the practice book and put your newfound knowledge to use. Because of your kind support, we’re motivated to continue creating workshops like this.

Please do not hesitate to contact us if you have any queries. Please let us know what you think in the comments area below.

The Exceldemy Team will constantly respond to your questions.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo