Concatenate Custom Function

[AJMTL]

Hi guys,

I need to concatenate the content of 3 cells using the separator "-" but only when cells are non empty

The custom function I'm trying to create would look like this :

Function ConcatWithSep(Sep As String, Str1 As String, Str2 As String, Str3 As String) As String
...
End Function

The function would be used like this :

=ConcatWithSep("-",B2,C2,D2)

The function would produce the following results :

Case #​	String 1​	String 2​	String 3​	FunctionResult​	ResultString lenght​
1​	X​	Y​	Z​	X-Y-Z​	5​
2​	X​	Y​	​	X-Y​	3​
3​	X​	​	Z​	X-Z​	3​
4​	​	Y​	Z​	Y-Z​	3​
5​	X​	​	​	X​	1​
6​	​	Y​	​	Y​	1​
7​	​	​	Z​	Z​	1​
8​	​	​	​	​

<tbody> </tbody>

Case #​	String 1​	String 2​	String 3​	Function Result​	Result String lenght​
1​	X​	Y​	Z​	X-Y-Z​	5​
2​	X​	Y​	​	X-Y​	3​
3​	X​	​	Z​	X-Z​	3​
4​	​	Y​	Z​	Y-Z​	3​
5​	X​	​	​	X​	1​
6​	​	Y​	​	Y​	1​
7​	​	​	Z​	Z​	1​
8​	​	​	​	​	0

<tbody>
</tbody>

I would very much appreciate any and all help and insights into this problem.

Many thanks,

Alex

 

[Snakehips]

AJMTL,

Maybe this....

Function ConcatWithSep(Sep As String, Str1 As String, Str2 As String, Str3 As String)
cws = Str1 & Sep & Str2 & Sep & Str3
For c = 1 To 2
If Right(cws, 1) = Sep Then cws = Left(cws, Len(cws) - 1)
If Left(cws, 1) = Sep Then cws = Right(cws, Len(cws) - 1)
Next c
ConcatWithSep = cws
End Function

Hope that helps.