VBA Concatenate Text Strings Together (& – Ampersand)

Associated Files Download Links

We have already gone over an introduction to string functions in our VBA Strings and Substrings Functions tutorial. We are now going to look at how to concatenate text strings.

Concatenate Strings

You can use the & operator in VBA to join text strings.

vba-concatenate

Concatenate Cells

You can also concatenate cells together. Below, we have the text strings in A1 and B1:

Concatenate Text Strings in VBA

The following code shows you how to join text strings from cell A1 and B1 using the & operator,  in cell C1:

The result is:

Using The Concatenate Operator in VBA to join Text Strings

This is the full procedure to concatenate two cells together (with variable declarations)

 

Using the & Operator with Spaces

When you want to include spaces you use & in conjunction with ” “. The following code shows you how you would include spaces:

The result is:

Concatenating Text in VBA with Spaces

Using the & Operator to Concatenate a Quotation Mark

Let’s say your text string contains a quotation mark, the following code shows you how to include a quotation mark within a text string:

The result is:

Using the Concatenate Operator to Concatenate a Quotation Mark

Putting Strings on a New line

Let’s say you have five text strings, you can put each text string on a new line or paragraph, using either the vbNewLine, vbCrLf, vbCr or Chr Function. The following code shows you how to put each text string on a new line:

The result is:

Using vbNewLine to Put Text on its own line in VBA

You may also like some of this related content...

Ads