how to concante a variable number of cells into one

dgr7

Board Regular
Joined
Apr 5, 2006
Messages
225
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
Here is a UDF

Code:
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

Board Regular
Joined
Apr 5, 2006
Messages
225
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
 

Forum statistics

Threads
1,181,607
Messages
5,930,881
Members
436,764
Latest member
avalladarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top