TL;DR —
Join(sourceArray, delimiter)collapses a one-dimensional array into one string, puttingdelimiterbetween each element. It is the exact inverse of Split: Split cuts a string into an array, Join glues the array back. It replaces thes = s & item & ","loop — and, unlike that loop, leaves no trailing delimiter. The catch: Join takes a 1-D array, not a Range and not a 2-D array, soJoin(Range("A1:A10").Value, ",")fails until you flatten the column to 1-D first.
Sub JoinDemo()
Dim parts As Variant, line As String
parts = Array("Jones", "Sarah", "Finance")
line = Join(parts, ",") ' glue with a comma between each
Debug.Print line ' Jones,Sarah,Finance <- no trailing comma
Debug.Print Join(parts) ' Jones Sarah Finance <- default delimiter is a SPACE
Debug.Print Join(parts, "") ' JonesSarahFinance <- empty delimiter = concatenate
End Sub
The signature — note the delimiter is optional and defaults to a space, not nothing:
Join(sourceArray, [delimiter])
' 1-D array default is " " (one space), not ""
What you'll learn
- The mental model — Join is Split run backwards
- Why Join replaces the concatenation loop (and kills the trailing-delimiter bug)
- The number-one trap: Join needs a 1-D array, so a Range or 2-D array fails
- The delimiter default is a space, and every element is coerced to text
- The Split-Filter-Join round trip that turns a loop into one line
The mental model: Join is Split, backwards
Hold Split and Join as a matched pair and both stop being fiddly. Split takes one
string with structure baked in — a CSV line, a Last,First name, a delimited log row — and hands you the
fields as an array. Join does the reverse: it takes those fields and welds them back into one string with
a separator of your choosing between them. If Split is "un-concatenate", Join is "concatenate this whole
list at once".
Because they are inverses, they compose cleanly. Split a line apart, change some pieces, Join it back:
Dim parts As Variant
parts = Split("Jones,Sarah,Finance", ",")
parts(2) = "Operations" ' edit one field
Debug.Print Join(parts, ",") ' Jones,Sarah,Operations
Why Join beats the concatenation loop
Everyone writes the string-building loop before they know about Join, and everyone writes the same bug:
' ⚠ The loop with the classic trailing-delimiter bug
Dim i As Long, s As String
For i = 0 To UBound(parts)
s = s & parts(i) & "," ' leaves "Jones,Sarah,Finance," — trailing comma
Next i
s = Left(s, Len(s) - 1) ' the ugly hack to chop it off
That trailing separator is not a rare edge case — it is every time, which is why the Left(s, Len(s) - 1) amputation is such a common sight. Join has no such problem because it puts the delimiter between
elements by design, never after the last one:
s = Join(parts, ",") ' Jones,Sarah,Finance — correct, first try
One call, no trailing character, no Len - 1 arithmetic, nothing to get wrong on an empty array either
(Join of an empty array is just "").
The number-one trap: Join needs a 1-D array, not a Range
This is where most "Join doesn't work" reports come from. Join accepts a one-dimensional array only. The moment you hand it something two-dimensional, it fails — and reading a multi-cell Range gives you exactly that:
' ⚠ FAILS — .Value of a multi-cell range is a 2-D array (10 rows x 1 col)
s = Join(Range("A1:A10").Value, ",") ' type mismatch / error 13
Range("A1:A10").Value is not a list — it is a 10 x 1 two-dimensional variant array, and Join has no
idea what to do with two dimensions. You must flatten the column to 1-D first. The idiomatic flatten
is Application.Transpose, which drops the singleton dimension of a single column:
Dim flat As Variant
flat = Application.Transpose(Range("A1:A10").Value) ' 2-D column -> 1-D array
s = Join(flat, ",") ' now Join is happy
That is exactly why Transpose sits in this cluster — it is the bridge from cells to
the 1-D array Join and Filter both require. (Watch its own limit: Application.Transpose caps at 65,536
elements, so flatten very tall columns with a loop instead.)
The delimiter default and text coercion
Two smaller surprises. First, if you omit the delimiter, Join uses a single space, not an empty
string — Join(parts) gives "Jones Sarah Finance". Pass "" explicitly when you want a straight
concatenation with nothing between.
Second, Join coerces every element to text. Numbers, dates and booleans are converted using your
system's regional settings — a Double may render as 1.5 or 1,5 depending on the machine, and a
date follows the local format. If you need a specific format (an invariant . decimal, an ISO date),
format each element with Format or CStr before you Join, rather than trusting the
locale. An array element that is an object or an uninitialised variant will raise an error rather than
coerce.
The opinion: never build a delimited string in a loop again
If you are concatenating array elements with & inside a For loop, you are writing a bug you will then
patch with Left(s, Len(s) - 1). Join exists precisely to delete that pattern. The rule is simple:
whenever the source is already an array, build the string with Join; whenever the source is in cells,
flatten it to a 1-D array first, then Join — do not loop.
And keep the pair in mind as a pipeline. Split, Filter, Join compose into one honest expression that reads like the sentence you would say out loud — "take this line, keep the fields mentioning 2026, and join them with commas":
result = Join(Filter(Split(line, ","), "2026"), ", ")
Split parses, Filter subsets, Join reassembles — three built-ins, no loop, no off-by-one.
When the string plumbing outweighs the point — describe the job instead
Joining is never the actual goal. The goal is "build the email subject line from these fields", or "write one delimited row per record to a log". By the time you have flattened the range, formatted each number so the decimals survive, and picked the delimiter, the plumbing is longer than the idea. ExcelMaster lets you describe the output in plain English — "make a semicolon-separated list of the client names for each region" — and it generates Python that reads the cells, formats them predictably, and backs up your file before it writes — you describe the output, and it handles the flattening, the number formats, and the delimiters.
Frequently asked questions
What does Join do in VBA?
Join(sourceArray, delimiter) takes a one-dimensional array and returns a single string with delimiter
placed between each element. Join(Array("a", "b", "c"), ",") returns "a,b,c". It is the inverse of
Split, and it never leaves a trailing delimiter after the last element.
Why does VBA Join give a type mismatch on a range?
Because Range("A1:A10").Value is a two-dimensional array (rows by columns), and Join accepts a
one-dimensional array only. Flatten the column first with Application.Transpose(Range("A1:A10").Value),
which turns a single-column 2-D array into a 1-D array, then pass that to Join.
What is the default delimiter in VBA Join?
A single space. Join(parts) with no delimiter returns the elements separated by spaces, not run
together. Pass an empty string, Join(parts, ""), when you want a straight concatenation with nothing
between the elements.
How do I join an array without a trailing comma in VBA?
Use Join instead of a loop. Join(parts, ",") puts the comma only between elements, so there is
never a trailing separator to strip. The s = s & item & "," loop always leaves one, which is why people
then chop it off with Left(s, Len(s) - 1) — Join removes the need entirely.
Can VBA Join handle numbers and dates?
Yes, but it coerces each element to text using your regional settings, so decimals and date formats
follow the local machine. For a predictable result, format each element with Format or CStr before
joining rather than relying on the locale. Objects or uninitialised variants in the array cause an error.
Tested in
Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-03.
Related guides: VBA Split · VBA Filter · VBA Transpose · VBA Format · VBA For Loop