Shakil Ahmed
In our daily use of Excel, we face situations where concatenating strings and integers becomes a necessity. In these articles, we showed ways of concatenating numbers and concatenation texts. Today we are going to show you how to concatenate string and integer using VBA. For this session, we are using Excel 2019, feel free to use your preferred version.
First things first, let’s get to know about the dataset which is the base of our examples.
Here, we have a table that contains several random people with their respective country codes and area codes. You can see that two types of data, both strings, and integers, are present within our table. Using this dataset, we will concatenate strings and integers.
Note that, this is a basic dataset to keep things simple. In a real-life scenario, you may encounter a much larger and complex dataset.
Practice Workbook
You are welcome to download the practice workbook from the link below.
Concatenate String and Integer Through VBA
In the following sections, we will demonstrate several ways to concatenate strings and integers using VBA code. For making it convenient for users from various categories, we will go step by step.
First, we will show ways to concatenate strings only. Then concatenate only integers. Finally, concatenate a string with an integer. You can go alongside us, or feel free to check your desired section using the table of contents.
1. VBA Code to Concatenate Strings
I. Concatenate Through Ampersand Operator
In this section, we will develop a VBA code that will concatenate strings using the ampersand sign (&) as concatenating operator.
Here, we have First Name and Last Name columns that have strings.
We will concatenate values from these two columns and will store the values in a newly introduced column Full Name.
First, open the Microsoft Visual Basic for Applications window. The shortcut key is ALT + F11.
Now, right-click on the project section. You will see an option called Insert. Click Module within Insert.
A code window /console will appear in front of you. Copy the following code and paste it into the code window.
Sub Concatenate_StringAmpersand()
Dim Str1, Str2 As String
Dim Result As String
Str1 = Range("B4").Value
Str2 = Range("C4").Value
Result = Str1 & " " & Str2
Range("D4").Value = Result
End Sub
Here, we have declared three strings (Str1, Str2, and Result. To make you understand the different use of these strings we have written them separately).
Then we have set the values for Str1 and Str2. The Range function lets us provide value from a specific cell. Using this function, we have inserted values from B4 and C4 cells into Str1 and Str2 variables respectively.
The next step is concatenating the strings. Using the ampersand sign (&) we have concatenated Str1 and Str2. To make it reader-friendly a space is provided that separates the two strings. Concatenating the strings, we stored in Result.
Finally, we have set the Result in D4.
Run the code, you will find the concatenated strings in the provided cell like the above image.
Make sure to adjust the cell references according to your dataset.
II. Concatenate Using Addition Operator
Two (or more) strings can be concatenated through the addition operator (+). Let’s use this addition operator or plus symbol in our VBA code to concatenate strings.
You know how to open the code window, use the following code there.
Sub Concatenate_StringPlus()
Dim Str1, Str2 As String
Dim Result As String
Str1 = Range("B4").Value
Str2 = Range("C4").Value
Result = Str1 + " " + Str2
Range("D4").Value = Result
End Sub
The code is similar to that of the previous section. There is a slight change and that too in a single line.
Result = Str1 + " " + Str2 is the line of code where we have made the change compared to the earlier one. We have used addition operators among the strings to concatenate them.
Run the code, you will find a string, formed by concatenating the provided strings.
III. Concatenate a Range of Strings
You may understand that we need to manually change the cell references every time to get the result for a new pair of strings.
For example, if we want to concatenate the strings from the next row of our table, we need to update the code as shown in the following image.
This time we are dealing with the 5th row. Then running the code we will find the concatenated string.
To skip the tedious task we can build a code that will concatenate the strings in the range.
Sub Concatenate_StringRange()
Dim i As Integer
For i = 4 To 9
Cells(i, 4).Value = Cells(i, 2) & " " & Cells(i, 3)
Next i
End Sub
Here, we have declared an integer i. And using this integer we initiate a loop to iterate over the cells.
Since our strings are within the 4th to 9th row. We have iterated i in such a way.
Cells is another way (we have used Range earlier) of indicating the cell in the Excel sheet. The first parameter of this function is for rows and the second one is for columns.
So initially Cells(i, 2) denotes 4th row in the 2nd column and Cells(i, 3) denotes 4th row from 3rd column. We concatenated these two cells using the ampersand sign (&).
After every iteration, the value of i gets updated. And continue up to 9.
Run the code, you will find that all the concatenated strings in the Full Name column at one go (image above).
2. VBA Code to Concatenate Integers
In this section, we will concatenate integers using VBA code. For example, we will concatenate Country Code and Area Code to form Dialing Code.
Here Country Code and Area Code are stored as integer numbers.
I. Concatenate Integers Through Ampersand Operator
Similar to the concatenation of the strings, we can use the ampersand sign to concatenate integers.
Let’s write the code first.
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
We have declared two integers Int1 and Int2 and a string Result. Using the VBA Range function we have inserted values from E4 and F4 cells into Int1 and Int2 variables respectively.
Then we have concatenated the two integer variables using ampersand sign (&) and stored them in the Result variable. And the final output is at the G4 cell.
Run the code, you will find the two integers in a concatenated form.
II. Concatenate Integers Through Addition Operator
We have used an addition operator for strings. Can we use it for the integers? Let’s use it and clear the doubt.
Let’s see the code
Sub Concatenate_IntegerPlus()
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
Hope you have understood the code. We have made a change, compare to the previous code, used plus sign (+) instead of ampersand sign (&).
Let’s run the code.
Oh dear! Not the result we wanted! It performed a conventional mathematic addition. So, we can’t use the addition operator to concatenate integers.
Don’t conclude so fast. Yes, we can not directly concatenate integers using the plus symbol but there is a way.
We need to typecast the integers into strings before using the addition operator (+). Let’s write the code that may help you understanding better.
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
We have introduced a couple of strings; Str1 and Str2. Within these strings, we will store the typecasted integer values.
You have noticed a couple of lines having CStr. This is a function that returns the provided value as a string. This Microsoft Document site can be helpful for knowing about CStr and other typecasting functions as well.
So, in Str1 = CStr(Int1) and Str2 = CStr(Int2) we have converted Int1 and Int2 into string and stored them within Str1 and Str2 respectively.
Once the integers are in form of a string, then we can use the addition operator.
Our code provided the value of concatenated integers.
III. Concatenate a Range of Integers
Again, similar to the strings, we can use a loop within our VBA code to concatenate integers over a range of cells at once.
Sub Concatenate_IntegerRange()
Dim i As Integer
For i = 4 To 9
Cells(i, 7).Value = Cells(i, 5) & Cells(i, 6)
Next i
End Sub
The code is similar to the code we have used while concatenating the range of strings. Here, the code is written in such a way that, it will only focus on concatenating the value of the cells we choose irrespective of their type.
Run the code, you will find the concatenated integers in the Dialing Code column at once.
3. Concatenate String with Integer using VBA
In this section, we will see how to concatenate string with integer using VBA code.
To show the example, we have introduced a new column Info. We will join Full Name (String) and Area Code (Integer) to complete the column.
I. Using Ampersand Operator
Obviously, this operator comes to your mind while concatenating. Let’s write the code using ampersand sign (&).
Sub Concatenate_StringInteger()
Dim Str1 As String
Dim Int1 As Integer
Dim Result As String
Str1 = Range("D4").Value
Int1 = Range("F4").Value
Result = Str1 & " " & Int1
Range("G4").Value = Result
End Sub
If you understood the previous code, then you have understood this code at first sight.
We have declared integer Int1 and a couple of strings Str1 and a string Result. Then inserted values from D4 and F4 cells into Str1 and Int1 variables respectively.
Then we have concatenated the variables using ampersand sign (&) and stored them in the Result variable. And the final output is at the G4 cell.
Run the code, you will find the string and integer in a concatenated form.
II. Using Addition Operator
We have concatenated strings using the addition operator and converted integers to strings to utilize the operator for concatenating. So, this operator will be in use for concatenating strings and integers together.
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
We converted the integer into a string and then concatenated that with another string variable Str1.
This worked perfectly. We have concatenated string and integer.
4. Concatenate Only Selected Cells
If you only want to concatenate selected cells then this section will be helpful for you.
First of all, to let Excel understand when to trigger the concatenate operation we need to insert a button. You will find the Button option within Insert in the Control section of the Developer tab.
Here, we have created a button and named it Concatenate.
We need to assign a code in this button, that will trigger every time we click the button.
Open the code window and write the following code.
Sub ConcatMacro()
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
End Sub
We have written the code in such a way that, for every selected cell it will traverse two columns, left of the active cell. And concatenate them. RC stands for row and column respectively.
Now, assign the macro to the button.
Right-click on the button, you will see an option called Assign Macro. Click that. Then select the macro from the Assign Macro dialog box.
Now select a cell and click the Concatenate button.
We have selected the D4 cell and clicked the button, and It concatenated the values from B4 and C4.
We can do this for any cell we aim.
Doesn’t matter whether the cells are consistent in serial or not.
We can use this button for concatenation Country Code and Area Code as well. But we may lose the format.
We can generate the Info column but as we have written the code that would traverse two columns at the left of the active cell, we need to make the Full Name and Area Code adjacent.
Conclusion
That’s all for today. We have listed several approaches that use VBA to concatenate string and integer in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.
