Author: Oscar Cronquist Article last updated on October 18, 2018

The JOIN function concatenates a set of substrings in an array, the image shows a user-defined function entered in cell C11.

=JoinStrings(B3:B7,C9)

Excel Function VBA Syntax

JOIN(sourcearray, [delimiter])

Arguments

sourcearray Required. A one-dimensional array.
[delimiter] Optional. A delimiting character, the default value is " " (space character).

Comments

The following user-defined function demonstrates the JOIN function.

Function JoinStrings(rng As Range, del As String)

rng1 = Application.Transpose(rng.Value)
JoinStrings = Join(rng1, del)

End Function

The JoinStrings function lets you use two arguments, the first one being a cell range and the second one a text string. The JOIN function concatenates the substrings in array variable rng1 using the delimiting character found in variable del.