Concatenate many rows quickly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cmotes@gmail.com - 30 Mar 2008 21:01 GMT

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.

Reply to this Message

David Biddulph - 30 Mar 2008 21:16 GMT

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

> Can someone tell me how to write a function that will:
>
[quoted text clipped - 4 lines]
>
> Thank you.

Reply to this Message

Gary''s Student - 30 Mar 2008 21:19 GMT

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.

Signature

Gary''s Student - gsnu2007g

> Can someone tell me how to write a function that will:
>
[quoted text clipped - 4 lines]
>
> Thank you.

Reply to this Message

Gord Dibben - 30 Mar 2008 21:27 GMT

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

>Can someone tell me how to write a function that will:
>
[quoted text clipped - 4 lines]
>
>Thank you.

Reply to this Message

Teethless mama - 30 Mar 2008 23:15 GMT

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

> Can someone tell me how to write a function that will:
>
[quoted text clipped - 4 lines]
>
> Thank you.

Reply to this Message

T. Valko - 31 Mar 2008 00:09 GMT

> (a) concatenate 1000 rows of data

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

Signature

Biff
Microsoft Excel MVP

> Download and install the free add-in Morefunc.xll from:
> http://xcell05.free.fr/english/
[quoted text clipped - 11 lines]
>>
>> Thank you.

Reply to this Message