how to concante a variable number of cells into one

[dgr7]

hello,

I'm hoping someone can help me with this formula or VBA question:
How would I write a concatenation-type statement that will be placed in all the rows of column B where there's data in column C, etc. in a worksheet and concatenate the data found in the same row in columns C through ? into one long string of data. It's a ? because the last cell of data in any particular row varies...it could be J, it could be R, etc. etc.

ex.
row 2's B column will have the contents of C through J
row 3's B column will have the contents of C through R
row 4's B column will have the contents of C through AB
etc.

thanks in advance,
david

 

[Jonmo1]

just write out the formula for the longest amount of colums...per your example, that would be column AB

so

=A1&B1&C1&D1&E1 etc.... &AB1

For row 1, when it get's to K, it will just add blanks...no difference..

 

[brian.wethington]

Here is a UDF

Public Function ConcateRange(InputRange, Optional delim As String = "/") As String

Dim arr As Variant
Dim Output As String
Dim i As Long
Dim j As Integer

arr = InputRange
For i = 1 To InputRange.Rows.Count
    For j = 1 To InputRange.Columns.Count
        If arr(i, j) <> vbNullString Then Output = Output & delim & arr(i, j)
    Next j
Next i
ConcateRange = Right(Output, Len(Output) - Len(delim))

End Function

Not sure about your knowledge of a UDF so to put this in:

right click on a sheet tab and select view code. right click on a sheet in the workbook you need it in and mouse over insert and select module. Paste the code there.

To use this function:

=ConcateRange(Maxpossiblerange)

This will put a "/" as your delim, you can choose any delim in this way.

=ConcateRange(Maxpossiblerange, " ")
This would be a space.

Hope this is helpful.

 

[dgr7]

Thank You both for your help. I've gone with Brian's solution...I prefer the VBA code approach, and it works nicely

thanks again,
david