Joining a range of cells in excel using &

0
votes

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 & ?

3
excelvba
Sorry to bear bad news but no, not without VBA. However, there are loads of sample UDFs (User Defined Functions) on this site and others for reference that serve this purpose. I recommend you pick one that uses Join and optionally allows the joining delimiter as a parameter in the function. – user4039065
rather annoying that Excel CONCATENATE function can't take an array as input. this should be a clean kill with a formula – brettdj

3 Answers

4
votes

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

       Concatenate a Range with Formula

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.

       Concatenate a Range with UDF

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.

1
votes

For a 1D range as you have (a single column or row), you could try this

For a Row, your example, it would be called like

=StrCat(A1:P1, TRUE)

Function StrCat(rng1 As Range, bRows As Boolean) As String
If bRows Then
    'row
    StrCat = Join(Application.Transpose(Application.Transpose(rng1)), ", ")
Else
    'column
    StrCat = Join(Application.Transpose(rng1), ", ")
End If
End Function
1
votes

I know this is an old question but I found a way to use Concatenate like @brettdj was talking about. Using the method from this link you can join together a range using a combination of Concatenate and Transpose like so:

Let say the cells you want to combine are in B2:B19.

In a blank cell, where you want to concatenate all the values type =CONCATENATE(TRANSPOSE(B2:B19))

Don’t press enter yet.

Select the TRANSPOSE(B2:B19) portion and press F9. This replaces the TRANSPOSE(B2:B19) with its result

Now remove curly brackets { and }

Press Enter & you're Done!

Demo from link:
Image
(source: chandoo.org)