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]

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?

The OP indicated the concatenation desired was:

="'"&B3&"', "&"'"&B4&"',"&"'"&B5&"',"&"'"&B6&" .....

so the first delimiter is ' and the second is ',

You (and I) may not see a useful need for what is returned by this, but that's what the OP requested.

 

[Tommeck37]

Hello,

thank you JoeMo very much. It works very good for me

Regards
Tommeck37

 

[JoeMo]

Hello,

thank you JoeMo very much. It works very good for me

Regards
Tommeck37

You are welcome - thanks for the reply.