Concatenate many rows quickly

  • Thread starter Thread starter cmotes
  • Start date Start date
C

cmotes

Can someone tell me how to write a function that will:

(a) concatenate 1000 rows of data (into a single cell)
(b) delimit each of those rows with a ;

The "A1&";"&B1&..." method is painfully slow, even with copy paste.

Thank you.
 
The following UDF will concatenate any range of cells into a single cell:

Function spliceUm(r As Range) As String
spliceUm = ""
For Each rr In r
spliceUm = spliceUm & rr.Value & ";"
Next
End Function

After you install the UDF, you can use it like:

=spliceUm(A1:D11)
or
=spliceUm(1:1)

etc.
 
A1 & B1 is concatenating columns, not rows.

Note: you can enter 32767 characters in a cell but you will see or print only
about 1024 characters in that cell so you won't get much out of putting that
much text in a single cell.

Whatever the case, try this UDF.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sbuf = sbuf & cell.text & ";"
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: =ConCatRange(A1:A1000)

This UDF is to be copied and pasted into a general module in your workbook.

Alt + F11 to open VBEditor. Ctrl + r to open Project Explorer.

Right-click on your workbook/project and Insert>Module.

Paste into that module.

Alt + q to return to the Excel Window.

Enter the formula into a cell.


Gord Dibben MS Excel MVP
 
(a) concatenate 1000 rows of data

Note that MCONCAT is *limited* to a return of 255 characters including the
delimiter.
 
Back
Top