vba - Joining a range of cells in excel using &

Question

I know I can concatenate a set of cells using & like this:

A1 & B1 & C1 & D1. Is there a way to write this for a range A1:P1 using & ?

Answer 1

You can accomplish this with a formula in a series of columns (each sequentially concatenating from the previous) or with a UDF (User Defined Function).

Formula Method

???????

The formula in F2 is,

=A2

The formula in G2 is,

=IF(LEN(B2), F2&","&B2, F2)

Fill G2 right to catch all of the available text values.

VBA UDF Method

Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ? Module (Alt+I,M). Paste the following into the new pane titled something like Book1 - Module1 (Code).

Function udf_stitch_Together(r As Range, Optional d As String = ", ") As String
    Dim s As String, c As Range
    For Each c In r
        If CBool(Len(c.Text)) Then _
            s = s & IIf(Len(s), d, vbNullString) & c.Text
    Next c
    udf_stitch_Together = s
End Function

Tap Alt+Q to return to your worksheet.

???????

The syntax is simply,

udf_stitch_Together(<range to concatenate>, <[optional] delimiter>)

For your purposes this would be,

=udf_stitch_Together(A2:P2)

??... or,

=udf_stitch_Together(A2:P2, ", ")

Fill down as necessary. Note that I have used =udf_stitch_Together(A5:P5, ";") in F5:F6 to demonstrate the ability to change the delimiter.