Concatenate a range of cells

Topic

New Reply

Is there a way to capture a range of cells to be merged into one cell without doing the following:

=concatenate(A2,”,”,B2,”,”,C2 etc)?

I was hoping for something that would work more efficiently like =concatenate(A2:F2) and somehow throw a comma between each entry. I’ve inherited a rather large database that isn’t working well for me so I need to merge the data in quite a few cells to accomplish my goal.

Reply | Quote

Replies

This VBA function does the trick.
Paste the code below into a normal module:

Code:

Public Function MyJoin(Range2Join As Range, Delimiter As String) As Variant
	Dim vValues As Variant
	Dim lCt As Long
	Dim sValues() As String
	vValues = Range2Join.Value
	ReDim sValues(1 To UBound(vValues, 2))
	For lCt = LBound(vValues, 2) To UBound(vValues, 2)
		sValues(lCt) = CStr(vValues(1, lCt))
	Next
	MyJoin = Join(sValues, ",")
End Function

Use the routine like this in a cell:
=MyJoin(A1:F1,”,”)

Reply | Quote

Thank you very much for that. I pasted your code in a module as instructed but I’m getting a #NAME? error in the cell that I’m entering the =MyJoin formula in. As I’m not “fluent” in code, is there something in there that disallows text? That is what I’m attempting to join together.

Reply | Quote

Are you sure you used a normal module in the same workbook as where you want to concatenate the cells? It works fine for me.
And if this is an existing workbook, make sure macros are enabled.

Reply | Quote

Works for me, and VERY useful too! Thanks Jan Karel.

Reply | Quote

It looks like this only works if the range to be concatenated is a row, but will not work if the data is in a column.

The Delimiter is ignored in the function.
the line of :
MyJoin = Join(sValues, “,”)

should be:
MyJoin = Join(sValues, Delimiter)

The following will concatenate any continuous range.

Code:

Public Function JoinIt(rngJoin As Range, strDelim As String) As Variant
Dim rngCell As Range, output As Variant
For Each rngCell In rngJoin
	output = output & rngCell & strDelim
Next
JoinIt = Left(output, Len(output) - Len(strDelim))
End Function

Reply | Quote

Thanks to all for contributing – this truly is valuable.

I’ve managed to have Jan Karel’s code work (and it’s brilliant!!) when I’m concatenating a row but not a column as Mike has noted. The cells I’m joining are in fact in a column.

Again, as I’m not well-versed in code, I’m unclear how to use the pieces of code provided. If I use Mike’s (and the MyJoin formula from Jan Karel) I get the #NAME error again concatenating a column. I’m missing something here – what am I doing wrong?

Reply | Quote

Since you’ve gotten it to work once, you know you’ve put it in the correct location for that workbook.

The function needs to be in the same workbook in order to be called using only its name or the =JoinIt(range,delim). You could put the function in your Personal.xls. You would then be able to call the function from each workbook you open. If you choose to go the “personal” route you would call the function using =Personal.xls!JoinIt(range,delim)

Reply | Quote

Deb: Mike’s function is called JoinIt, not MyJoin, which is why you would get the #Name! error if you use MyJoin.

Mike: well spotted that I forgot to use the delimiter. One of those last-minute additions which I did not fully implement.

Reply | Quote

Thank you, thank you!! All is well. This is the biggest time-saver I’ve had in a long time – many thanks again Mike and Jan Karel.

Reply | Quote