How to Concatenate String and Integer using VBA

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 strings and integers 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.

Dataset - VBA Concatenate String And Integer

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.

Introducing new column for VBA Concatenate String

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.

Insert - Module - VBA Concatenate String And Integer

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

 

VBA code to Concatenate strings

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.

Concatenated Strings through VBA

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

 

Addition Operator to Concatenate Strings

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.

Concatenated Strings through VBA - VBA Concatenate String And Integer

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.

Change cell references repeatedly - VBA Concatenate String And Integer

This time we are dealing with the 5th row. Then running the code we will find the concatenated string.

Result after changing cell references-VBA Concatenate String And Integer

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

 

Concatenate a range of cells of strings with VBA -VBA Concatenate String And Integer

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.

Concatenate result a range of cells of strings with VBA

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.

Introducing column to store concatenated integers

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

 

VBA code to concatenate Integers

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.

Concatenated Integer result

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

 

Code to concatenate integer using plus symbol

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.

Result of using addition operator

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

 

Updated Code to concatenate integer using plus symbol

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.

Result of using updated code 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

 

Concatenate a range of cells of integers with VBA

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.

Concatenate result a range of cells of integers with VBA

3. Concatenate String with Integer using VBA

In this section, we will see how to concatenate strings with integers using VBA code.

Introducing column to store concatenated strings &integers

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 

 

Code to concatenate string and integer - VBA Concatenate String And Integer

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.

Concatenate result integers strings with VBA

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

 

Code to concatenate string and integer using plus

We converted the integer into a string and then concatenated that with another string variable Str1.

Concatenate result integers strings with VBA

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.

Button - VBA Concatenate String And Integer

Here, we have created a button and named it Concatenate.

Button name - VBA Concatenate String And Integer

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

 

Code for button - VBA Concatenate String

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.

Assign code - VBA Concatenate String And Integer

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.

select macro from Assign Macro box - VBA Concatenate String And Integer

Now select a cell and click the Concatenate button.

Select cell - Press button  - VBA Concatenate String And Integer

We have selected D4 cell and clicked the button, and It concatenated the values from B4 and C4.

We can do this for any cell we aim.

Select cell - Press button 2 - VBA Concatenate String And Integer

Doesn’t matter whether the cells are consistent in serial or not.

Select cell - Press button 3 - VBA Concatenate String And Integer

We can use this button for concatenation Country Code and Area Code as well. But we may lose the format.

Button press for Integer String concatenate

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.

Button press for concatenating Strings & Integers

Conclusion

That’s all for today. We have listed several approaches that use VBA to concatenate strings and integers 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.

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

ExcelDemy
Logo