How to Concatenate String and Integer with VBA (5 Easy Ways)

Without a doubt, VBA Macros help to solve various problems and automate repetitive tasks in Microsoft Excel. Now, concatenating strings and integers is one such task that we can automate using Excel’s VBA. In this article, we’ll explore all the nitty-gritty of how to concatenate string and integer with VBA. In addition, we’ll also learn to combine strings and variables with VBA.

Download Practice Workbook

5 Ways to Concatenate String and Integer with VBA

First and foremost, let’s consider the Employee Information dataset shown in the B4:E10 cells which contains the “First Name”, “Last Name”, “Country Code” and “Area Code” columns respectively. Here, we want to combine the “First Name” and the “Last Name” using VBA Code. So, without further delay, let’s see each method in detail and with the appropriate illustration.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Concatenate Strings

First of all, let’s concatenate Strings using the Ampersand and the Addition operators in VBA.

1.1 Using Ampersand Operator

For one thing, we can use the Ampersand operator in VBA, as we use it in the Excel spreadsheet, to join strings of text.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button.

Now, this opens the Visual Basic Editor in a new window.

• Second, go to the Insert tab >> select Module.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

Sub Concatenate_StringAmpersand()

Dim Str1, Str2 As String
Dim Result As String

Str1 = Range("B5").Value
Str2 = Range("C5").Value

Result = Str1 & " " & Str2
Range("D5").Value = Result
End Sub

• Third, close the VBA window >> click the Macros button.

This opens the Macros dialog box.

• Following this, select the Concatenate_StringAmpersand macro >> hit the Run button.

Lastly, the results should look like the image given below.

1.2 Utilizing Addition Operator

Alternatively, we can also use the Addition operator to join strings in VBA.

📌 Steps:

• At the very beginning, follow the Steps from the previous method to open the Visual Basic editor and enter the code.

Sub Concatenate_StringPlus()

Dim Str1, Str2 As String
Dim Result As String

Str1 = Range("B5").Value
Str2 = Range("C5").Value

Result = Str1 + " " + Str2
Range("D5").Value = Result
End Sub

• Next, click the Macros button >> choose the Concatenate_StringPlus macro >> hit the Run button.

Finally, the output should look like the picture given below.

1.3 Concatenating a Range of Strings

Now, in the previous method, we have to manually combine the string of text which can be tiresome if there are lots of cells. So, let’s automate this repetitive task.

📌 Steps:

• First of all, follow the steps shown in the prior method to insert the code into the Module.

Sub Concatenate_StringRange()

Dim i As Integer

  For i = 4 To 10
    Cells(i, 4).Value = Cells(i, 2) & " " & Cells(i, 3)
  Next i

End Sub

• Not long after, press the Macros button >> choose Concatenate_StringRange >> click on Run.

Boom! That is how simple it is to concatenate a range of strings.

2. Concatenate Integers

In a similar manner, we can also concatenate Integers by utilizing the Ampersand and the Addition. So, let’s see the process in detail.

2.1 Applying Ampersand Operator

In truth, applying the Ampersand operator to join integers is remarkably similar to the previous method, so just follow along.

📌 Steps:

• In the first place, insert the code into the Module.

Sub Concatenate_Integer()

Dim Int1, Int2 As Integer

Dim Result As String

Int1 = Range("E5").Value
Int2 = Range("F5").Value

Result = Int1 & Int2
Range("G5").Value = Result

End Sub

• Second, execute the Concatenate_Integer macro.

Eventually, your output should appear in the screenshot below.

2.2 Employing Addition Operator

Likewise, we can employ the Addition operator in VBA to concatenate two Integers.

📌 Steps:

• Initially, copy and paste the following code into the Module window.

Sub Concatenate_IntegerPlus()

Dim Int1, Int2 As Integer
Dim Str1, Str2 As String

Dim Result As String

Int1 = Range("E5").Value
Int2 = Range("F5").Value
Str1 = CStr(Int1)
Str2 = CStr(Int2)


Result = Str1 + Str2
Range("G5").Value = Result

End Sub

• Following this, select the Concatenate_IntegerPlus macro >> press Run.

Ultimately, the final output should look like the figure shown below.

2.3 Combining a Range of Integers

Now, you may be wondering, is there a way to concatenate integers spanning a range of cells? Then VBA has you covered. Just follow along.

📌 Steps:

• To begin with, follow the Steps shown above to paste the VBA code.

Sub Concatenate_IntegerRange()

Dim i As Integer

  For i = 4 To 10
    Cells(i, 7).Value = Cells(i, 5) & Cells(i, 6)
  Next i

End Sub

• In turn, click on the Concatenate_IntegerRange macro to run the block of code.

Consequently, this generates the results shown in the image below.

3. Concatenate Strings and Integers

Additionally, we can combine the Strings and Integers with the help of the Ampersand and Addition operators.

3.1 Using Ampersand Operator

By the same token, we’ll define the variables and join them with the Ampersand operator.

📌 Steps:

• First of all, enter the following code in the VBA window.

Sub Concatenate_StringInteger()

Dim Str1 As String
Dim Int1 As Integer
Dim Result As String

Str1 = Range("D5").Value
Int1 = Range("F5").Value

Result = Str1 & " " & Int1
Range("G5").Value = Result
End Sub

• At this time, run the Concatenate_StringInteger macro.

Subsequently, the text “John Mathew 907” appears in the Info column.

3.2 Utilizing Addition Operator

By now you’ve guessed that the next method involves using the Addition operator. Therefore, allow us to demonstrate the process in the steps below.

📌 Steps:

• To start with, type in the following code.

Sub Concatenate_StringIntegerPlus()

Dim Str1, Str2 As String
Dim Int1 As Integer
Dim Result As String

Str1 = Range("D5").Value
Int1 = Range("F5").Value
Str2 = CStr(Int1)

Result = Str1 + " " + Str2
Range("G5").Value = Result
End Sub

• Now, run the Concatenate_StringIntegerPlus macro.

Ultimately, this returns the string “John Mathew 907”.

4. Concatenate Only Selected Cells

Besides, we can also concatenate the Strings and Integers present in the selected cells. It’s simple and easy, just follow the steps.

📌 Steps:

• First, navigate to the Developer tab >> click the Insert drop-down >> in the Form Controls option, choose the Button.

• Second, rename the button to “Concatenate”.

• Third, copy and paste the macro into the Module window.

Sub ConcatMacro()

    ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

• In turn, right-click on the “Concatenate” button >> go to the Assign Macro option.

• Fourth, select ConcatMacro from the list >> hit the OK button.

• Fifth, select the D5 cell and click the “Concatenate” button to obtain the text “John Mathew”.

In a similar manner, we can also combine the selected Integer and String values as shown below.

5. Concatenate Entire Column

Moreover, we can VBA concatenate string and integer using the TEXTJOIN function which combines text strings with a delimiter.

📌 Steps:

• Initially, apply the code in the VBA editor.

Sub Concatenate_Entire_Column()
Range("D5") = WorksheetFunction.TextJoin(" -", True, Range("B5:C5"))
Range("D6") = WorksheetFunction.TextJoin(" -", True, Range("B6:C6"))
Range("D7") = WorksheetFunction.TextJoin(" -", True, Range("B7:C7"))
Range("D8") = WorksheetFunction.TextJoin(" -", True, Range("B8:C8"))
Range("D9") = WorksheetFunction.TextJoin(" -", True, Range("B9:C9"))
Range("D10") = WorksheetFunction.TextJoin(" -", True, Range("B10:C10"))
End Sub

• Later, execute the Concatenate_Entire_Column macro.

Lastly, the “Book Name” and “Author” text are combined as shown in the screenshot below.

How to Concatenate String and Variable with VBA

Last but not least, we can combine a string and variable using VBA code in Excel. Now, allow us to demonstrate the procedure in the following steps.

📌 Steps:

• First of all, insert the following code into the Module.

Sub Concatenate_Text_String_and_Variable()

Dim r_rng As Range
Set r_rng = Range("B5:D10")

Dim col_num() As Variant
col_num = Array(1, 2, 3)

delimiter = ", "

Result = "E5"

For x = 1 To r_rng.Rows.Count
    output_rng = ""
    For y = LBound(col_num) To UBound(col_num)
        If y <> UBound(col_num) Then
            output_rng = output_rng & r_rng.Cells(x, Int(col_num(y))) & delimiter
        Else
            output_rng = output_rng & r_rng.Cells(x, Int(col_num(y)))
        End If
    Next y
    Range(Result).Cells(x, 1) = output_rng
Next x

End Sub

• Second, launch the Concatenate_Text_String_and_Variable macro by hitting the Run button.

Eventually, the final output is seen in the “Combined” column.

Admittedly, we’ve skipped some of the relevant examples of concatenating strings and variables, which you might explore if you want.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice VBA concatenate string and integer. Please make sure to do it by yourself.

Conclusion

In essence, this article shows 5 effective methods how to concatenate string and integer with VBA. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.

Further Readings

• How to Concatenate Multiple Cells in Excel (8 Quick Approaches)