Author: Oscar Cronquist Article last updated on May 20, 2022

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.

JOIN function examples

test
Filter duplicate words from a cell range [UDF]
AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]
test
Filter unique distinct words from a cell range [UDF]
This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]
test
Filter unique words from a range [UDF]
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]
test
How to use the JOIN function [VBA]
The JOIN function concatenates a set of substrings in an array, the image shows a user-defined function entered in cell […]
test
Sort values in a cell based on a delimiting character
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

Excel VBA Reference

This extensive vba reference list provides detailed information including syntax, arguments, return values, and examples for Visual Basic for Applications.