How to Concatenate Strings in Excel VBA?

In this post, you will learn how to concatenate strings in your Excel spreadsheet programmatically using Excel VBA.

Concatenate Strings in Excel VBA

Concatenate strings is merging or adding two or more texts together. In excel VBA, we can concatenate strings and the strings in the cells. Concatenation can be performed using the following methods.

  • Using the & Operator with Spaces
  • Using the & Operator to Concatenate a Quotation Mark
  • Putting Strings on a New line

Concatenate Strings in MsgBox

To concatenate strings in a MsgBox,

Code:

MsgBox "Merge" & "Text"
How to Concatenate Strings in Excel VBA?

Concatenate strings in Cells

To concatenate strings in cells,

Code:

Range("C1").Value = Range("A1").Value & Range("B1").value

When you need to perform the same operation with a variable declaration, use the below code

Code:

Sub ConcatenateStrings()

Dim FirstString as String

Dim SecondString as String

FirstString = Range("A1").Value

SecondString = Range("B1").Value                                                                        

Range("C1").Value = FirstString & SecondString

End Sub
How to Concatenate Strings in Excel VBA?

Using the & Operator with Spaces

To concatenate strings using the & operator with spaces,

Code:

Sub Merge_Text()

Dim FirstString As String

Dim SecondString As String

Dim Result As String

FirstString = "Concatenate"

SecondString = "Strings"

Result = FirstString & " " & SecondString

MsgBox Result

End Sub
How to Concatenate Strings in Excel VBA?

Using the & Operator to Concatenate a Quotation Mark

Let’s imagine when you need to concatenate a string and a quotation mark. Here is the code to concatenate strings and quotation mark.

Code:

Sub StringWithQuotation()

Dim FirstString As String

Dim SecondString As String

Dim Result As String

StringOne = "String concatenation with quotation mark"

StringTwo = """"

StringThree = FirstString & " " & SecondString

MsgBox Result

End Sub
How to Concatenate Strings in Excel VBA?

Leave A Reply

Your email address will not be published. Required fields are marked *

You May Also Like

In this post, you’ll learn how to Find Blank cells in excel so that you can remove or delete the blank cells form...
In this article, you’ll learn what is a Gauge Chart in Microsoft Excel. Also, you will learn how to add...
Microsoft Excel provides a shortcut for the users to move columns in excel using two different ways – using Shift...