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.

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.
--
David Biddulph

wrote in message
...
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.
--
Gary''s Student - gsnu2007g


" wrote:

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.

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

Paste into that module.

Alt + q to return to the Excel Window.

Enter the formula into a cell.


Gord Dibben MS Excel MVP



On Sun, 30 Mar 2008 13:01:38 -0700 (PDT), wrote:

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.

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


" wrote:

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.

(a) concatenate 1000 rows of data

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


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
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


" wrote:

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.