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.

Using Ampersand Operator in VBA to Concatenate Strings

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

Inserting 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

VBA concatenate string and integer code

⚡ 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.

Code Explanation for vba concatenate string and integer

Close the VBA window >> click the Macros button.

This opens the Macros dialog box.

Select the Concatenate_StringAmpersand macro >> click the Run button.

Running VBA macro

The result will be as shown in the image below:

vba concatenate string and integer using Ampersand Operator

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

Utilizing Addition Operator to Concatenate Strings

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

Running VBA code with Addition Operator

The result will be as shown in the image below:

vba concatenate string and integer utilizing Addition Operator

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

Concatenating a Range of Strings

⚡ 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.

Running VBA for Concatenating a Range of Strings

The result is as shown in the image below:

vba concatenate string and integer concatenating a Range of Strings

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

Applying Ampersand Operator to Concatenate Integers

⚡ 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.

Executing code for Ampersand Operator

The output will be as shown in the image below:

vba concatenate string and integer applying Ampersand Operator

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

Employing Addition Operator to Concatenate Integers

Select the Concatenate_IntegerPlus macro >> press Run.

The result will be as shown in the image below:

vba concatenate string and integer employing Addition Operator

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

Combining a Range of Integers

Click on the Concatenate_IntegerRange macro and run the code.

The result will be as shown in the image below:

vba concatenate string and integer combining a Range of Integers

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

Using Ampersand Operator for Concatenating Strings and Integers

⚡ 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.

Running code to Concatenate Strings and Integers

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

vba concatenate string and integer using Ampersand Operator

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

Utilizing Addition Operator for Concatenating Strings and Integers

⚡ 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.

Running VBA code for Concatenating Strings and Integers

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

vba concatenate string and integer Utilizing Addition Operator

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:

Concatenate Only Selected Cells

Rename the button to “Concatenate”.

Inserting concatenate button

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

VBA code to Concatenate Only Selected Cells

⚡ 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.

Assigning macro

Select ConcatMacro from the list >> Click OK.

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

vba concatenate strings for selected cells

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

vba concatenate string and integer for selected cells

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

VBA code to Concatenate Entire Column

⚡ 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:

vba concatenate string and integer with TEXTJOIN function

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

How to Concatenate String and Variable with VBA

⚡ 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.