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.
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:
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
⚡ 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.
- 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
⚡ 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.
Boom! That is how simple it is to concatenate 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
⚡ 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.
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.
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
⚡ 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.
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
⚡ 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.
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
⚡ 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.
- 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.
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
⚡ 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.
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
⚡ 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.
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.
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.