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

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. We will combine the “First Name” and the “Last Name” using VBA Code.

vba concatenate string and integer

Method 1 – Concatenate Strings

We will concatenate Strings using the Ampersand and the Addition operators in VBA.

1.1 Using Ampersand Operator

Use the Ampersand operator in VBA to join strings of text.

Steps:

Navigate to the Developer tab >> click the Visual Basic button.

Go to the Insert tab of the Visual Basic Editor >> select Module.

Enter the following code:

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

⚡ Code Breakdown:

The sub-routine is given a name, it is Concatenate_StringAmpersand().
Define the variables Str1, Str2 and Result and assign String data type.
Store the values of the B5 and C5 cells in the Str1 and Str2 variables using the Range object.
Combine the two variables with the Ampersand operator and return the result in the D5 cell.

Close the VBA window >> click the Macros button.

This opens the Macros dialog box.

Select the Concatenate_StringAmpersand macro >> click the Run button.

The result will be as shown in the image below:

1.2 Utilizing Addition Operator

Steps:

Follow the Steps from the previous method to open the Visual Basic editor and enter the following 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

Click the Macros button >> choose the Concatenate_StringPlus macro >> click the Run button.

The result will be as shown in the image below:

1.3 Concatenating a Range of Strings

Steps:

Enter the following code in 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

⚡ Code Breakdown:

The sub-routine is given a name, Concatenate_StringRange().
Define the variable i as an Integer datatype.
Use a For Loop to iterate through the B5:B10 and C5:C10 range of cells and return the result in D5:D10 cells.

Press the Macros button >> choose Concatenate_StringRange >> click on Run.

The result is as shown in the image below:

Method 2 – Concatenate Integers

Concatenate Integers by utilizing the Ampersand and the Addition.

2.1 Applying Ampersand Operator

Steps:

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

⚡ Code Breakdown:

The sub-routine is given a name, Concatenate_Integer().
Define the variables Int1, Int2, and Result and assign the Integer and String data types.
Store the values of the E5 and F5 cells in the Int1 and Int2 variables.
Combine the two variables with the Ampersand operator and return the result in the G5 cell.

Execute the Concatenate_Integer macro.

The output will be as shown in the image below:

2.2 Employing Addition Operator

Steps:

Enter 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

Select the Concatenate_IntegerPlus macro >> press Run.

The result will be as shown in the image below:

2.3 Combining a Range of Integers

Steps:

Enter the following VBA code in the module:

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

Click on the Concatenate_IntegerRange macro and run the code.

The result will be as shown in the image below:

Method 3 – Concatenate Strings and Integers

3.1 Using Ampersand Operator

Define the variables and join them with the Ampersand operator.

Steps:

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

⚡ Code Breakdown:

Name the macro, Concatenate_StringInteger().
Define the variables Str1, Int1, and Result and assign the Integer and String data types.
Store the values of the D5 and F5 cells in the Str1 and Int1 variables.
Combine the two variables with the Ampersand operator and return the result in the G5 cell.

Run the Concatenate_StringInteger macro.

The text “John Mathew 907” will appear in the Info column as shown in the image below:

3.2 Utilizing Addition Operator

Steps:

Enter the following code in the VBA module:

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

⚡ Code Breakdown:

Name the macro, Concatenate_StringIntegerPlus().
Define the variables Str1, Str2, Int1, and Result and assign the Integer and String data types.
Store the values of the D5 and F5 cells in the Str1 and Int1 variables.
Use the CStr function to convert the Int1 variable to a string and store it in the Str2 variable.
Combine the Str1 and Str2 variables with the Addition operator and return the output in the G5 cell.

Run the Concatenate_StringIntegerPlus macro.

The string “John Mathew 907” will appear in G5 as shown in the image below:

Method 4 – Concatenate Only Selected Cells

Steps:

Navigate to the Developer tab >> click the Insert drop-down >> in the Form Controls option, choose the Button as shown in the image below:

Rename the button to “Concatenate”.

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

⚡ Code Breakdown:

Name the macro, ConcatMacro().
Use ActiveCell.FormulaR1C1 property to concatenate the cell values of the B5 and C5 cells.
Use the Selection application to format the cell properties like HorizontalAlignment, VerticalAlignment, etc.

Right-click on the “Concatenate” button >> go to the Assign Macro option.

Select ConcatMacro from the list >> Click OK.

Select the D5 cell and click the “Concatenate” button to obtain the text “John Mathew”.

We can combine the selected Integer and String values as shown below:

Method 5 – Concatenate Entire Column

Steps:

Enter the following 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

⚡ Code Breakdown:

Enter a name for the macro, Concatenate_Entire_Column().
Use the WorksheetFunction object to call the TextJoin function to join the strings in the selected range, like the B5:C5 range with a Hyphen delimiter.

Execute the Concatenate_Entire_Column macro.

The “Book Name” and “Author” text will be combined as shown in the image below:

How to Concatenate String and Variable with VBA

Steps:

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

⚡ Code Breakdown:

Define the macro name, Concatenate_Text_String_and_Variable().
Assign Range and Variant datatypes to the r_rng and col_num variables.
Store the B5:D10 range in the r_rng variable, 3 arrays for the col_num variable, choose the comma delimiter and set the Result variable to the E5 cell.
Use the If statement within For loop to iterate through the chosen range and join the string of text with the Ampersand operator.