EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA Concatenate
Secondary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String

VBA Concatenate

By Madhuri ThakurMadhuri Thakur

Excel VBA Concatenate

Excel VBA Concatenate

Concatenation can be defined as joining or adding two strings or data items to get a single string or data item is called concatenation. In simple words, if we have a list of first names in one column and last name in another column with the help of concatenation operation we can combine both and place in a single cell within a fraction of seconds. In Excel, to achieve this concatenation we have a worksheet function called Concat(). But, this kind of function is not available in VBA. We cannot use concatenate() in VBA coding as it will not work. So, there are no functions available in VBA and cannot access the worksheet functions too, then how we will concatenate two or more strings in VBA.

First, we will see how we will do with worksheet function then we will see the same in VBA. Consider two strings in excel as shown in below screenshot.

VBA Example 1

Now use concatenate function to combine both the strings.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA Concat

Observe the formula, D4 and E4 are the address of the cells that we want to combine. Like above we can concatenate multiple numbers of strings from different cells.

How to Use Excel VBA Concatenate Function?

We will learn how to use a VBA Concatenate with few examples in Excel.

You can download this VBA Concatenate Excel Template here – VBA Concatenate Excel Template

VBA Concatenate – Example #1

As we do not have any built-in functions in VBA, concatenation in VBA can be achieved by using ampersand (&) operator.

We will take the same example which is already we took for the worksheet function. We have “I love” in cell D4 and “India” in cell E4.  Now we will combine those two strings in VBA. Go to the VBA editor tab.

Step 1: Go to the Developer tab and then select the “visual basic” tab on the left-hand side. Then it will take to the below screen.

VBA Developer CCT

Step 2: First, we need to create a subprocess with any name like concatenation. To initiate subprocess, use keyword Sub and process name “concatenation”.

Code:

Sub Concatenate()

End Sub

VBA Concatenate Example 1.1

Step 3: In Subprocess we need to define strings like string1, string2, and full_string using the keyword dim.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String

End Sub

VBA Concatenate Example 1.2

Step 4: Now, we need to assign the string “I love” to string1 and “India” to string2 using “=” assignment operator as below.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String

String1 = "I Love"
String2 = "India"

End Sub

 Example 1.3

Step 5: Now, combine the string1 and string2 using ampersand operator and assign that combination to full_string as below. Keep a space between string variables and ampersand operator to avoid error message.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String

String1 = "I Love"
String2 = "India"
full_string = String1 & String2

End Sub

Example 1.4

Step 6: Now, concatenation of both string1 and string2 is stored in full_string. Display this string using a message box as below.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String

String1 = "I Love"
String2 = "India"
full_string = String1 & String2

MsgBox (full_string)

End Sub

 Example 1.5

Step 7: Now, it’s time to execute the process. Click on the play button which is marked with red color box. The result will appear in the message box as below.

VBA Example concatenate 2

Step 8: In the above example we took the two strings directly in the program and there is no space between the first string and second string. How to add space then? Very simple while concatenating, concatenate space also.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = "I Love"
String2 = "India"
full_string = String1 & " " & String2

MsgBox (full_string)

End Sub

VBA CCT Example 1.7

Step 9: Observe the above image we added the space between string1 and string2 with the help of double-quotes. In case if we want to add ‘-‘ we can do that also.

Code:

Sub Concatenate()

Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = "I Love"
String2 = "India"
full_string = String1 & "-" & String2

MsgBox (full_string)

End Sub

VBA CCN Example 1.7

Step 10: The result will be as below.

VBA concatenate 3

VBA Concatenate – Example #2

Step 1: Now we will take the data from the excel sheet and concatenate and then display results. For that assign the data in the cells to string1 and string2 as shown below.

Code:

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String

String1 = Cells(4, 4).Value
String2 = Cells(4, 5).Value

MsgBox (full_string)

End Sub

VBA Concatenate Example 2.1

Step 2: Observe the above image, we assigned the value in cells(4,4) to stirng1 and cells(4,5) in string2. As usual, concatenate both strings using the ampersand operator.

Code:

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String

String1 = Cells(4, 4).Value
String2 = Cells(4, 5).Value
full_string = String1 & "  " &  String2

MsgBox (full_string)

End Sub

VBA Excel Example 2.4

Step 3: Because of this whatever the data in cells(4,4) and Cells(4,5) will combine and store in full_string. Now execute the process by clicking on play symbol.

VBA concatenate 5

Step 4: The data is taking from excel shown below.

VBA Excel 2.5

Step 5: If we change the data in the excel and re-run the program results will change as per the excel data. Instead of India, I changed to sweets now we will run and check.

VBA concatenate 4

Step 6: Now the result is coming in the message box. Instead of a message box if we want in excel itself we can do that.

Code:

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(4, 4).Value
String2 = Cells(4, 5).Value
Cells(4, 7).Value = String1 & String2

MsgBox (full_string)

End Sub

VBA Excel 2.5

Step 7: Assign the concatenation in cell address cell(4,7) and remove msgbox as we no more need of message box. Press the play button and go to you excel and check will get the result in cell(4,7) (row 4 and column 7).

VBA Example 2.6

Step 8: As we are adding two strings we can use “+” symbol also as below.

Code:

Sub Concatenate2()
Dim String1 As String
Dim String2 As String
Dim full_string As String
String1 = Cells(4, 4).Value
String2 = Cells(4, 5).Value
Cells(4, 7).Value = String1 + String2

End Sub

Example 2.7

Step 9: Result will be as below. There will be no difference.

Example 2.8

Step 10: But if we use numbers instead of strings to concatenate using “+” symbol it will perform concatenation only not addition because we took the data type as a string, not an integer.

Example 2.9

Step 11: Result will be as below.

VBA concatenate 5

In case if we are taking the data type as integer then we should use ampersand operator only for concatenation. If we use “+” while taking integer data type, then it will not concatenate but perform addition.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,328 ratings)

VBA Concatenate – Example #3

Step 1: If we want to combine a string and number also we can apply the same technique. For that keep number value in one cell and string value in another cell and click on the run option.

 Example 3.1

Step 2: The result will not have an impact it will be the same string result as earlier with the only change is number instead of a string.

VBA concatenate 7

Things to Remember

  • Concatenation is to combine the strings of two or more multiple strings of different cells. To perform this in Excel, we have inbuilt worksheet function that is CONCAT.
  • Worksheet function concat is not accessible in VBA to concatenate.
  • Concatenation will be achieved with the help of operator ampersand (&) and plus (+). If you are taking integer data type, then “+” symbol will perform addition operation instead of concatenation hence ensure on that.
  • Use space before and after the & and + symbols otherwise it will throw an error message.
  • When defining string variables, do not keep any space in a variable name. If any space provided in string variable name it will not allow as it will throw error message as below.

Recommended Articles

This is a guide to VBA Concatenate. Here we discuss how to use Excel VBA Concatenate Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA InStr
  2. VBA Integer
  3. VBA Select Cell
  4. VBA Transpose
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more