Concatenate dynamic range

[Tommeck37]

Hello my dear experts,


I am looking for your help.


Can anyone help me create a formula that would concatenate strings that are in one dynamic column.
I would like to concatenate in one cell all the strings that are input in column B. Moreover, the number of item in column B is variable.


What I managed to do is to write a concatenate formula that links all the strings together. However the problem is that if the cells are empty in column B the formula still puts separating characters " ' " and " , " which is not needed if the cells in B are empty.


Please find my file attached to this post


Best Regards
Tommeck37




[TABLE="width: 500"]
<tbody>[TR]
[TD]ISIN[/TD]
[TD]="'"&B3&"', "&"'"&B4&"',"&"'"&B5&"',"&"'"&B6&"',"&"'"&B7&"',"&"'"&B8&"',"&"'"&B9&"',"&"'"&B10&"',"

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="width: 87"]CA123456789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="width: 87"]GB123456789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Here's a UDF you can try.

Function JoinCellsData(rng As Range, Sep1 As String, Sep2 As String) As String
JoinCellsData = ""
If TypeName(rng) <> "Range" Then Exit Function
'Omit blank cells in rng
 For Each c In rng
    If c.Value <> vbNullString Then
        JoinCellsData = JoinCellsData & Sep1 & c.Value & Sep2
    End If
Next c
'strip off last separator
If Len(JoinCellsData) > 0 Then
    JoinCellsData = Left(JoinCellsData, Len(JoinCellsData) - Len(Sep2) + 1)
Else
    MsgBox "No data in the range entered to concatenate"
End If

End Function

An example of use with your delimiters to concatenate A2:A21 would be:
=JoinCellsData(A2:A21,"'","',")

 

[netuser]

That's nice function Jemo, just wondering how can I use double quote as separator ? """ is giving error

EDIT : Found it you have to put 4 double quotes """"

 

[Rick Rothstein]

Here's a UDF you can try.

Function JoinCellsData(rng As Range, Sep1 As String, Sep2 As String) As String
JoinCellsData = ""
If TypeName(rng) <> "Range" Then Exit Function
'Omit blank cells in rng
 For Each c In rng
    If c.Value <> vbNullString Then
        JoinCellsData = JoinCellsData & Sep1 & c.Value & Sep2
    End If
Next c
'strip off last separator
If Len(JoinCellsData) > 0 Then
    JoinCellsData = Left(JoinCellsData, Len(JoinCellsData) - Len(Sep2) + 1)
Else
    MsgBox "No data in the range entered to concatenate"
End If

End Function

An example of use with your delimiters to concatenate A2:A21 would be:
=JoinCellsData(A2:A21,"'","',")

Why the Sep1 argument? If one is specified, it will appear at the beginning of the returned value as the first character(s)... is that what you intended? Personally, I think just the one delimiter would be sufficient. Here is how I would probably write it...

[table="width: 500"]
[tr]
	[td]Function JoinCells(Rng As Range, Delimiter As String) As String
  JoinCells = Replace(Replace(Application.Trim(Replace(Replace(Join(Application.Transpose(Rng), Delimiter), " ", Chr(1)), Delimiter, " ")), " ", Delimiter), Chr(1), " ")
End Function[/td]
[/tr]
[/table]

 

[netuser]

Rick with 1 delimiter you wont have Text in between the delimiter, or maybe I don't understand how to enter it correctly ?

here is the result for both fucntions : (B3 is yours and I is not looking good )

Excel 2010 32 bit
	A	B
1	Title
2	test	"test","VV","VVV","ZZZ","BBB","GGG","BBB","FFF","GG","DDD","OOO","PPP"
3	VV	test",VV",VVV",ZZZ",BBB",GGG",BBB",FFF",GG",DDD",OOO",PPP
4	VVV
5	ZZZ
6	BBB
7	GGG
8	BBB
9
10	FFF
11	GG
12
13	DDD
14
15
16	OOO
17	PPP
Feuil13

Cell Formulas
Range	Formula
B2	=JoinCellsData(A2:A21,"""",""",")
B3	=JoinCells(A2:A21,""",")

 

[Rick Rothstein]

Rick with 1 delimiter you wont have Text in between the delimiter, or maybe I don't understand how to enter it correctly ?

here is the result for both fucntions : (B3 is yours and I is not looking good )

Excel 2010 32 bit
	A	B
1	Title
2	test	"test","VV","VVV","ZZZ","BBB","GGG","BBB","FFF","GG","DDD","OOO","PPP"
3	VV	test",VV",VVV",ZZZ",BBB",GGG",BBB",FFF",GG",DDD",OOO",PPP
4	VVV
5	ZZZ
6	BBB
7	GGG
8	BBB
9
10	FFF
11	GG
12
13	DDD
14
15
16	OOO
17	PPP
Feuil13

Cell Formulas
Range	Formula
B2	=JoinCellsData(A2:A21,"""",""",")
B3	=JoinCells(A2:A21,""",")

Before I tell you how, I have a question... are all your cells single words like your example above shows? If so, I can simplify my code dramatically (right now, my code assumes there can be two or more words in a single cell).

 

[netuser]

I am not the OP I was just pointing out the difference between 2 functions. Sorry

 

[Rick Rothstein]

I am not the OP I was just pointing out the difference between 2 functions. Sorry

Ah, I missed that. Assuming multiple words are possible in a cell, I would write the formula that uses my function this way in order to have all the cell values quoted like you showed...

=""""&JoinCells(A1:A21,""",""")&""""

 

[JoeMo]

Why the Sep1 argument? If one is specified, it will appear at the beginning of the returned value as the first character(s)... is that what you intended? Personally, I think just the one delimiter would be sufficient. Here is how I would probably write it...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function JoinCells(Rng As Range, Delimiter As String) As String
  JoinCells = Replace(Replace(Application.Trim(Replace(Replace(Join(Application.Transpose(Rng), Delimiter), " ", Chr(1)), Delimiter, " ")), " ", Delimiter), Chr(1), " ")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

It works and for me is a whole lot faster to write and understand than the one-liner you suggested.

 

[Rick Rothstein]

It works and for me is a whole lot faster to write and understand than the one-liner you suggested.

I understand your position on the "one-liner" (which is why I wrote probably in bold) and can respect that, but my question to you dealt with your function having two delimiter arguments... I don't understand why two of them. Let's say cells A1, A2 and A3 held the numbers 1, 2 and 3 respectively, and using an @ sign and * sign for visibility, this formula...

=JoinCellsData(A1:A3,"@","*")

would return this...

@1*@2*@3*

I really cannot see any kind of general need for such an arrangement of delimiters. True, when you default one of them to the empty string as you suggested to the OP in Message #2, you get a useful arrangement (namely because it is equivalent to a single delimiter function), but I cannot see a useful need for what is returned when both delimiters are specified as non-empty strings. Am I missing something here?