Repeating a concatenate function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Topher - 17 Aug 2006 18:30 GMT

Any ideas out there? I have a spreadsheet with an unknown number of rows
(1-100). I am trying to concatenate in one cell the output of each row. I
can use a count function to tell me how many rows to concantenate, but is
there some type of repeat function that will move down one row each time?
ie. if i have the following names in Column A, I want this result in Column C.

"A" "B" "C"
Carl Carl, Nick, Fred, Sam
Nick
Fred
Sam

Thanks in advanced

Signature

Topher
If you have questions, Someone has answers

Reply to this Message

Mike - 17 Aug 2006 18:40 GMT

I would just write a macro to loop round the appropriate number of
times - looping is something Excel isn't good at doing but if you
combine the power of simple macros and the functionality of Excel,
there's not much you can't achieve. If you haven't used macros before,
simply record one and edit the results. The syntax for addressing
particular cells and creating loops is pretty straightforward.

Mike

> Any ideas out there? I have a spreadsheet with an unknown number of rows
> (1-100). I am trying to concatenate in one cell the output of each row. I
[quoted text clipped - 9 lines]
>
> Thanks in advanced

Reply to this Message

Mike - 17 Aug 2006 18:40 GMT

Reply to this Message

David Billigmeier - 17 Aug 2006 18:41 GMT

You can create a UDF to perform this. To do this follow these steps:

1) Alt-F11
2) In the left pane right click on your sheet name and choose <Insert><Module>
3) Paste the following code:

Function concat(rng As Range)
For Each rng In rng
temp = temp & rng & ", "
Next
concat = Left(temp, Len(temp) - 2)
End Function

4) Then you can use the function concat() within your sheet (e.g.
=concat(A1:A10))

Signature

Regards,
Dave

Reply to this Message

Gord Dibben - 17 Aug 2006 20:12 GMT

David

A small problem with your UDF..............

If any cells are blank in the A1:A10 range you get extra commas.

I.e. a,b,c,,,g,h,i,j

This modified UDF does not have that problem.

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

Gord Dibben MS Excel MVP

>You can create a UDF to perform this. To do this follow these steps:
>
[quoted text clipped - 11 lines]
>4) Then you can use the function concat() within your sheet (e.g.
>=concat(A1:A10))

Reply to this Message

Harlan Grove - 17 Aug 2006 20:36 GMT

Gord Dibben wrote...
...

>This modified UDF does not have that problem.

...

But it's not general. Why not make the comma delimiter another
argument? But then again, why restrict the udf unnecessarily to ranges?
Also, what happens if the OP *wants* to include blank fields between
comma separators? Choice is good.

The mcat udf in

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/94
456a9e326b19a6

(or http://makeashorterlink.com/?C6B84239D ) would allow for variable
number of arguments, and could be used for this specific task in an
array formula like

=SUBSTITUTE(TRIM(mcat(A1:A8&" "))," ",", ")

or just

=SUBSTITUTE(mcat(A1:A8&" ")," ",", ")

if blank fields should be included.

Reply to this Message