In Excel VBA, Join combines the items in a one-dimensional array into one string, placing a chosen delimiter between them. Its syntax is Join(sourcearray, [delimiter]); if you omit the delimiter, VBA uses a space.
What the VBA Join function does
Join is a Visual Basic for Applications function, not a worksheet formula. It is useful when a macro has several text values and needs to turn them into a single string—for example, a comma-separated list, a line of labels, or a value to write to a text file.
The function does not combine worksheet cells directly. It expects a one-dimensional array as its first argument. A range containing several cells usually returns a two-dimensional array from its Value property, so you need to convert that data to a one-dimensional array before passing it to Join.
The result is a string. Join does not change the original array, add quotation marks around values, or apply special formatting to numbers and dates. If the values need a particular display format, convert or format them before joining.
Syntax and delimiter behavior
The syntax is Join(sourcearray, [delimiter]). sourcearray is the required one-dimensional array of items to combine. delimiter is optional and specifies the text inserted between adjacent items.
- When the delimiter is omitted, the separator is a single space.
- Use a comma and a space, such as
", ", for a readable list. - Use
""for no separator at all. - A delimiter can contain more than one character, such as
" | "orvbCrLffor line breaks.
The delimiter appears between items, not before the first item or after the last. For example, joining three items with ", " produces first, second, third, not , first, second, third,.
A basic VBA Join example
This macro creates an array, joins its elements with a vertical bar, and displays the resulting string. The array returned by Array is suitable input for Join.
Sub JoinArrayExample()
Dim parts As Variant
Dim combined As String
' Create an array of text values.
parts = Array("North", "South", "West")
combined = Join(parts, " | ")
MsgBox combined
End Sub
The message box displays North | South | West. Change the second argument to ", " for a comma-separated result, or leave it out to get North South West.
You can also use a declared string array when the values are known in advance. For example, assign values to Dim words(0 To 2) As String and then call Join(words, "-"). This returns the three words separated by hyphens. The array must be one-dimensional; a two-dimensional array is not a direct substitute.
Joining values from a worksheet range
For a multi-cell range, Excel returns a two-dimensional array: the first dimension represents rows and the second represents columns. The following example assumes the values are in A2:A5. It copies nonblank, non-error values into a one-dimensional string array, then joins them with commas.
Sub JoinColumnValues()
Dim values As Variant
Dim parts() As String
Dim i As Long
Dim itemCount As Long
Dim combined As String
' A2:A5 is a multi-cell, single-column range.
values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5").Value
ReDim parts(0 To UBound(values, 1) - 1)
For i = 1 To UBound(values, 1)
If Not IsError(values(i, 1)) Then
If Len(CStr(values(i, 1))) > 0 Then
parts(itemCount) = CStr(values(i, 1))
itemCount = itemCount + 1
End If
End If
Next i
If itemCount = 0 Then
combined = ""
Else
ReDim Preserve parts(0 To itemCount - 1)
combined = Join(parts, ", ")
End If
MsgBox combined
End Sub
For example, if A2:A5 contains Red, a blank cell, Blue, and Green, the result is Red, Blue, Green. The code skips cells containing Excel errors and values whose string representation has zero length. A cell containing spaces is not considered blank by this test, so trim the text first if spaces should be ignored. The code assumes A2:A5 contains multiple cells; a single-cell range has a different value shape and needs separate handling.
Choosing separators and handling different values
Pick the delimiter based on where the result will be used. A comma and space is easy to read, while a tab may suit data copied into another application. For a multi-line message, use vbCrLf between items. If the output will be parsed by another system, confirm that the delimiter cannot also appear inside an item, or use an escaping or quoting scheme appropriate to that format.
When joining numbers or dates, VBA converts each value to text. The resulting representation can depend on the value and regional settings; a date may not have the format a report requires. To control the output, explicitly convert values before assigning them to the array—for instance, use VBA’s Format function for a date or numeric value. Also decide whether empty strings and blank cells should remain as empty positions or be omitted. Join does not have an “ignore blanks” option of its own.
For example, joining Array("A", "", "C") with ", " retains the empty middle item, producing A, , C. If that is undesirable, filter empty items before calling Join, as the range example does.
Common errors and troubleshooting
Many problems come from passing data in a shape or state that Join cannot use. Check these points when a macro fails or produces unexpected text:
- Array not initialized: A dynamic array that has not been assigned or sized does not contain elements to join. Populate or size it first.
- Wrong dimensions: A two-dimensional range array cannot be passed directly. Flatten the relevant row or column into a one-dimensional array.
- Cell errors: A worksheet error value such as
#N/Ais not ordinary text. Check withIsErrorand decide whether to skip it or substitute a label. - Unexpected spaces: An omitted delimiter inserts a space. Specify the delimiter explicitly when the required output has no separator or uses punctuation.
- Wrong bounds: Do not assume every array starts at index zero. Use
LBoundandUBoundwhen looping over an array whose lower bound is not guaranteed.
An array with zero usable items should be handled before calling Join, especially when it is built dynamically. Returning an empty string for that case makes the macro’s behavior clear. If the first argument is an array of Variants, ensure each element can be converted to text and handle error values deliberately.
Join versus the Excel TEXTJOIN function
Use VBA Join inside a macro or a VBA procedure. Use the worksheet function TEXTJOIN when the desired result belongs in a cell and should update as worksheet values change. TEXTJOIN is available in Microsoft 365 and Excel 2019 or later. Unlike VBA Join, it accepts ranges and includes an argument for ignoring empty cells.
With values in A2:A5, this worksheet formula joins them with comma-space separators and ignores empty cells:
=TEXTJOIN(", ",TRUE,A2:A5)
For example, if A2:A5 contains Red, a blank, Blue, and Green, the formula returns Red, Blue, Green. In older Excel versions without TEXTJOIN, a formula or VBA approach may be needed. A worksheet formula cannot call the built-in VBA Join function by simply typing =Join(...).
Using Join in a worksheet custom function
If you want to call joining logic from a cell, you can write a VBA user-defined function (UDF). This example accepts a range, skips blank cells and error values, and uses Join internally. Put it in a standard VBA module, not in a worksheet cell’s formula bar as code.
Public Function JoinCells(ByVal source As Range, _
Optional ByVal delimiter As String = ", ") As String
Dim cell As Range
Dim parts() As String
Dim itemCount As Long
ReDim parts(0 To source.Cells.Count - 1)
For Each cell In source.Cells
If Not IsError(cell.Value) Then
If Len(CStr(cell.Value)) > 0 Then
parts(itemCount) = CStr(cell.Value)
itemCount = itemCount + 1
End If
End If
Next cell
If itemCount = 0 Then
JoinCells = ""
Exit Function
End If
ReDim Preserve parts(0 To itemCount - 1)
JoinCells = Join(parts, delimiter)
End Function
With values in A2:A5, enter =JoinCells(A2:A5, ", ") in a worksheet cell to return a comma-separated string. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA. A UDF is useful for a reusable calculation, but it is not the same as a normal worksheet function: VBA macros do not run in Excel for the web, and users may need to enable macros in the desktop app according to their organization’s security rules.
Running and saving VBA safely
On Excel for Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure, and run it from the editor or through Excel’s macro controls. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On Excel for Mac, the Visual Basic Editor is also available, though keyboard shortcuts can differ by keyboard and system settings; Option+F11 or Fn+Option+F11 may be required. Excel for the web cannot create, edit, or run VBA macros. Before running any macro that changes workbook contents, save a copy of the workbook. Macro actions generally cannot be reversed with Ctrl+Z, so keep the original data recoverable.
Frequently Asked Questions
What is the default delimiter for VBA Join?
If you omit the delimiter argument, Join inserts a single space between array elements.
Can VBA Join combine cells directly?
No. It accepts a one-dimensional array, not a Range object. Read the range’s values and convert them to a one-dimensional array first, or use a worksheet function such as TEXTJOIN where available.
Does Join skip blank items automatically?
No. Empty elements remain in the output, so adjacent delimiters may appear. Filter blank elements before calling Join if you want to omit them.
Can I use Join as an Excel formula?
Not as a built-in worksheet formula. Use TEXTJOIN for a cell formula, or create a VBA UDF such as JoinCells and save the file in a macro-enabled format.
Why does Join fail with a range’s Value?
A multi-cell range usually produces a two-dimensional array, while Join requires one dimension. Copy the needed row or column values into a one-dimensional array, and handle empty arrays and error cells as needed.
Related
- Excel Split Cell Contents by Delimiter
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range in Excel: How to Reference CellsIn Excel VBA, a Range is an object that represents one cell or a group of cells. To…
- Excel VBA Error Codes: Meanings and FixesExcel VBA error codes identify problems that stop a macro or signal that an operation failed. The most…
- Excel VBA: Center an Image in a Cell Without DistortionTo center an image in an Excel cell with VBA, select the picture, run a macro that scales…
- Excel VBA String to Number: Safe Conversion MethodsIn Excel VBA, convert text that represents a number with a conversion function such as CDbl for decimals…
- Excel VBA Date to Number: Convert Dates to SerialsIn Excel VBA, convert a Date value to a number with CDbl(dateValue). The result is a serial value:…
- VBA MsgBox Default Button: Set It and Choose SafelyIn VBA, a MsgBox uses its first button as the default unless you specify otherwise. To make another…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.