Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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,",")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works for me, and VERY useful too! Thanks Jan Karel.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  7. #7
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •