VBA Join Function

The Excel VBA Join function returns a single string from an array of strings separated by a delimiter.

This is the opposite of the VBA Split function which splits a string into a VBA Array across a delimiter.

VBA Reference – Array Functions

VBA Join Function Syntax

1
Join( sourcearray [, delimiter ] )

Parameters

sourcearray
The one dimensional string array which elements you want to join into a single string.

delimiter
Optional. A delimiter that is to separate the joined string items in the array. By default this is a Space character.

Examples using the VBA Join functions

Simple concatenated string from Array

1
2
3
4
5
Dim arr As Variant, joinString as String
 
joinString = Join(Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly"))
 
Debug.Print joinString 'Result: "Dragon Dog DRAGONfly Cat fly"

Notice that all items are separated in the final string. This is because the default delimiter is a Space character.

In above example I also used the VBA Array function to quickly define a VBA Array of strings.

Below a simple example showing that only String arrays can be Joined:

1
2
3
4
5
Dim arr As Variant, joinString as String
 
joinString = Join(Array(1, 2, 3 )) 'ERROR: array is not a String array
 
joinString = Join(Array("1", "2", "3" )) 'OK. Result: "1 2 3"

Concatenated string using delimiter

We will modify code example above using a delimiter of our choosing:

1
2
3
4
5
Dim arr As Variant, joinString as String
 
joinString = Join(Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly"), ";")
 
Debug.Print joinString 'Result: "Dragon;Dog;DRAGONfly;Cat;fly"