Concatenate many rows quickly

[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.

 

[David Biddulph]

Copy, Edit/ Paste Special/ Transpose to turn your column into a row.
Set your Windows Regional Options to have semi-colon instead of a comma as
list separator, then save as CSV. Your CSV will have the original rows
separated by semi-colons.
You can then set your Windows Regional Options back to comma as separator.

 

[Gary''s Student]

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(A111)
or
=spliceUm(1:1)

etc.

 

[Gord Dibben]

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

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

=SUBSTITUTE(TRIM(MCONCAT(IF(A1:Z1<>"",A1:Z1,"")&" "))," ",";")

ctrl+shift+enter, not just enter

 

[T. Valko]

(a) concatenate 1000 rows of data

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