Concatenate an entire column into one cell? Module needed?

[raceonusa]

Basically what I need to do is the equivalent of typing this in once cell...

=A1&A2&A3&A4&A5&A6&A7&A8&A9&A10&A11... etc...

Is there an easy way to type this, or a formula that can do the same thing?

I've tried =A:A but this adds the total, I need to concatenate them

 

[mikerickson]

You could use a UDF. For your case the formula would be =ConcatRange(A:A)

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    With inputRange
        If Not (Application.Intersect(.Parent.UsedRange, .Cells) Is Nothing) Then
            For Each oneCell In Application.Intersect(.Parent.UsedRange, .Cells)
                If oneCell.Text <> vbNullString Then
                    ConcatRange = ConcatRange & delimiter & oneCell.Text
                End If
            Next oneCell
            ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
        End If
    End With
End Function

 

[Industry]

This UDF works really well.
When you select a range of multiple rows and colums, it concatenates the range row by row. I was wondering, could you make an alternative version that concatenates by columns?
Thanks

 

[mikerickson]

The UDF above is a simlified version of a different UDF, ConcatIf, which can be found here:
http://www.mrexcel.com/forum/showthread.php?t=441658&highlight=concatif

The double loop in ConcatIf, can be altered to do rows and columns in any desired order.

ConcatRange(aRange,Delimiter) is equivlant to
ConcatIf(aRange,"<>",aRange,Delimiter)

 

[kd81]

I would like to know if you could show me how to add commas between the numbers so that it will be displayed this way A1,A2,A3,A4.......

ie if A1=10 A2=20 A3=30 A4=40 ... it will show this way 10,20,30,40

Thank you!

 

[RoryA]

You just need to specify the delimiter:
=ConcatRange(A:A, ",")

for example.

 

[kd81]

It works.

Thnaks rorya!!

 

[coreyjames]

Is there away to make the =concatrange update automatically? If I am using a pivot table and the names in the concatenation change it is not automatically updating. Thanks!

 

[mikerickson]

The function ConcatRange should up date as if it were a normal Excel function.

 

[coreyjames]

I wish it did. The cells it is concatenating are an If statement looking at a pivot table. So when the cells change the concatenation should change. The only way to get it to change is when I click on the cell and hit enter in the formula box again.