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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Concatenate String and Integer with VBA: 5 Simple Ways

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.

vba concatenate string and integer

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.

Using Ampersand Operator in VBA to Concatenate Strings

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

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

Inserting 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

VBA concatenate string and integer code

Code Breakdown:

Here, we’ll explain the VBA code used to concatenate strings with the Ampersand operator.

  • First, the sub-routine is given a name, here it is Concatenate_StringAmpersand().
  • Next, define the variables Str1, Str2, and Result and assign String data type.
  • Then, store the values of the B5 and C5 cells in the Str1 and Str2 variables using the Range object.
  • Finally, combine the two variables with the Ampersand operator and return the result in the D5 cell.

Code Explanation for vba concatenate string and integer

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

Running VBA macro

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

vba concatenate string and integer using Ampersand Operator


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

Utilizing Addition Operator to Concatenate Strings

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

Running VBA code with Addition Operator

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

vba concatenate string and integer utilizing Addition Operator


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

Concatenating a Range of Strings

Code Breakdown:

Now, let’s understand the VBA code used to concatenate a range of strings.

  • First, the sub-routine is given a name, here it is Concatenate_StringRange().
  • Then, define the variable i as an Integer datatype.
  • Afterward, use a For Loop to iterate through the B5:B10 and C5:C10 range of cells and return the result in D5:D10 cells.

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

Running VBA for Concatenating a Range of Strings

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

vba concatenate string and integer concatenating a Range of Strings


2. Concatenate Integers

Similarly, 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

Applying Ampersand Operator to Concatenate Integers

Code Breakdown:

  • To start with, the sub-routine is given a name, here it is Concatenate_Integer().
  • Later, define the variables Int1, Int2, and Result and assign the Integer and String data types.
  • Next, store the values of the E5 and F5 cells in the Int1 and Int2 variables.
  • Lastly, combine the two variables with the Ampersand operator and return the result in the G5 cell.

  • Second, execute the Concatenate_Integer macro.

Executing code for Ampersand Operator

Eventually, your output should appear in the screenshot below.

vba concatenate string and integer applying Ampersand Operator


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

Employing Addition Operator to Concatenate Integers

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

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

vba concatenate string and integer employing Addition Operator


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

Combining a Range of Integers

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

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

vba concatenate string and integer combining a Range of Integers

Read More: How to Concatenate Range with Separator Using VBA in Excel


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

Using Ampersand Operator for Concatenating Strings and Integers

Code Breakdown:

  • Initially, name the macro, in this case, Concatenate_StringInteger().
  • Later, define the variables Str1, Int1, and Result and assign the Integer and String data types.
  • Now, store the values of the D5 and F5 cells in the Str1 and Int1 variables.
  • Finally, combine the two variables with the Ampersand operator and return the result in the G5 cell.

  • At this time, run the Concatenate_StringInteger macro.

Running code to Concatenate Strings and Integers

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

vba concatenate string and integer using Ampersand Operator


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

Utilizing Addition Operator for Concatenating Strings and Integers

Code Breakdown:

  • At the very beginning, give the macro a name, for instance, Concatenate_StringIntegerPlus().
  • Later, define the variables Str1, Str2, Int1, and Result and assign the Integer and String data types.
  • Afterward, store the values of the D5 and F5 cells in the Str1 and Int1 variables.
  • In turn, use the CStr function to convert the Int1 variable to a string and store it in the Str2 variable.
  • Eventually, combine the Str1 and Str2 variables with the Addition operator and return the output in the G5 cell.

  • Now, run the Concatenate_StringIntegerPlus macro.

Running VBA code for Concatenating Strings and Integers

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

vba concatenate string and integer Utilizing Addition Operator


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.

Concatenate Only Selected Cells

  • Second, rename the button to “Concatenate”.

Inserting concatenate button

  • 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

VBA code to Concatenate Only Selected Cells

Code Breakdown:

  • First, name the macro, here it is, ConcatMacro().
  • Next, use ActiveCell.FormulaR1C1 property to concatenate the cell values of the B5 and C5 cells.
  • Following this, use the Selection application to format the cell properties like HorizontalAlignment, VerticalAlignment, etc.

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

Assigning macro

  • 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”.

vba concatenate strings for selected cells

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

vba concatenate string and integer for selected cells

Read More: Excel VBA to Concatenate Cell Values 


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

VBA code to Concatenate Entire Column

Code Breakdown:

  • First and foremost, enter a name for the macro, for example, Concatenate_Entire_Column().
  • Now, 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.

  • Later, execute the Concatenate_Entire_Column macro.

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

vba concatenate string and integer with TEXTJOIN function

Read More: Macro to Concatenate Multiple Columns in Excel


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

How to Concatenate String and Variable with VBA

Code Breakdown:

  • In the first portion, define the macro name, here it is, Concatenate_Text_String_and_Variable().
  • Next, assign Range and Variant datatypes to the r_rng and col_num variables.
  • Then, 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.
  • In the second portion, nest the If statement within For loop to iterate through the chosen range and join the string of text with the Ampersand operator.

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

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

vba concatenate string and variable

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.

Practice Section


Download Practice Workbook


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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo