CONCATENATE and TEXTJOIN Functions are similar in terms of adding strings and integers. The only difference is setting the Delimiter in TEXTJOIN functions.
The CONCATENATE Function works great with VBA if you want to automate joining Strings and Integers together.
In this post, we will guide you through the detailed step of using this Combination function.
How to Convert Integer To String in VBA
When concatenating a string and an integer, it was important to consider the data types involved.
In many programming languages, including Excel’s VBA, the string and integer data types cannot be directly concatenated without converting the integer to a string.
The reason for this was that strings and integers are different data types with different representations in memory.
When you concatenate a string and an integer, you need to ensure that they are compatible.
How do you do that?
By converting the integer to a string representation.
That’s why we had to use the CStr() function inside the VBA Dialog box.
Note: The CStr() Function was specific to VBA and still not available as a built-in function in Excel’s worksheet formulas.
Code:
Dim myInteger As Integer Dim concatenatedString As String myInteger = 456 concatenatedString = "The number is: " & CStr(myInteger) MsgBox concatenatedString
Result: The number is: 456
The CStr() function is handy sometimes, but not always needed.
Now, Excel VBA automatically converts values to the appropriate data type when assigning them to a cell. So, you don’t need the CStr() function when assigning a string value to a cell’s Value property.
VBA Code To Concatenate Strings
Let’s Look at the example down below. We have a list of Book Names with the First and Last Names of the Author. Here, we want to join the two strings (First and Last Name).
Yes, You can use the & operator or the TEXTJOIN function here. But, do notice that, there won’t be any spaces between the two names.
So, the best VBA Code to Concatenate the first and last names will be:
Sub Concatenate_StringRange() Dim i As Integer For i = 2 To 10 Cells(i, 4).Value = Cells(i, 2) & " " & Cells(i, 3) Next i End Sub
Explanation of the Code:
This VBA code concatenates the values from columns B and C and places the result in column D for rows 2 to 10 in the Excel worksheet.
We used the Sub Concatenate_StringRange() line to declare the start of a VBA subroutine. The i variable will be used as a counter in the For loop. We have used i=2 to 10 because our dataset contained the values from row 2.
The Line Cells(i, 4).Value = Cells(i, 2) & ” ” & Cells(i, 3) concatenates a string and two cell values and assigns the result to a specific cell in the worksheet. Here, 2, 3, and 4 indicates column B, C, and D respectively.
Follow the steps below to initiate the code:
- Go to the Developer Tab and Select the Visual Basic Option.
- Insert a new module and paste the above code.
- Press the F5 Button to Run the Macro Code.
- Return to the Excel Spreadsheet to see the result.
Result:
Note: You can join only integers with the same formula. Just change the column and row numbers.
How To Concatenate Text Strings and Integers in Excel
Let’s go step by step with this code. First, we want to join a single text string and an integer.
Look at the image down below.
combine-single-text-with-an-integer
We want to combine D2 with E2 separated by a comma (,). So, go to the Visual Basic Option using Alt+F11 and paste the following code.
Sub Concatenate_StringInteger()
Range("G2").Value = Range("D2").Value & ", " & Range("E2").Value
End Sub
Run the command using F5.
Now, since the Fill handle won’t work here. We need the VBA Code to automatically Loop through the rows and concatenate the text and integers. That is, concatenating multiple rows.
For this we have to assign a For Loop in the code.
Sub Concatenate_StringInteger()
Dim i As Integer
For i = 2 To 6
Range("G" & i).Value = Range("D" & i).Value & ", " & Range("E" & i).Value & " pages"
Next i
End Sub
Result:
Explanation of the Code:
This VBA code loops through rows 2 to 6 in an Excel worksheet. For each row, it concatenates the values from columns D and E, adds a (comma) “, ” in between, and appends the word “pages” at the end. The resulting concatenated string is then placed in column G of the corresponding row.
VBA Code To Concatenate String and Variable
To concatenate a string and a variable in VBA for Excel, you can use the Ampersand (&) operator or the OFFSET function.
Look at the example below. We want to join the book name, author name, and the number of pages altogether in the column G.
combine-texts-and-variables-excel
The code will be lengthy. But don’t worry. You just need to modify only a few numbers.
VBA Code:
Sub Concatenate_Text_String_and_Variable()
Dim r_rng As Range
Set r_rng = Range("A2:A6, D2:E6")
Dim col_num() As Variant
col_num = Array(1, 4, 5)
Dim delimiter As String
delimiter = ", "
Dim Result As Range
Set Result = Range("G2")
Dim output_rng As String
Dim x As Long
For x = 1 To r_rng.Rows.Count
output_rng = ""
Dim y As Long
For y = LBound(col_num) To UBound(col_num)
If y <> UBound(col_num) Then
output_rng = output_rng & r_rng.Cells(x, col_num(y)).Value & delimiter
Else
output_rng = output_rng & r_rng.Cells(x, col_num(y)).Value
End If
Next y
Result.Offset(x - 1).Value = output_rng
Next x
End Sub
Places That Will Require Modification:
Firstly, you have to set the range of the data. We have used A2:A6, D2:E6 because we want to combine the data of these ranges only. So, our commanding line became Set r_rng = Range(“A2:A6, D2:E6”).
Next is col_num = Array(1, 4, 5). We will be combining the data from the Column A, D, and E that’s why we wrote col_num (1,4,5).
Finally, set the cell where you want your Result. In the commanding line Set Result = Range(“G2”), we used the cell G2 as the output cell. Modify it according to your needs.
Result:
Conclusion
Concatenation using Excel’s VBA serves as a great tool alternative to Ampersand (&), Addition Operators, and even TEXTJOIN functions.
With these easy codes you can easily join text strings and numbers together. Just copy and paste the codes to the Visual Basic Option and you are good to go.
