Sometimes it is necessary to join the contents of cell with custom characters like +,-,:,|, etc. This can be achived by using excel inbuilt function CONCATENATE. The problem with this function is you will have to specify the character every time. The function listed below will help to simplify this problem.
The function can take multiple arguments just like =SUM() function.
Example:
=CONCATENATE_WITH_CUSTOM_CHARACTER("+", a1:c1,b8)
the result: all the cell values of a1:c1 and b8 will will be displayed seperated by "+" sign
Function CONCATENATE_WITH_CUSTOM_CHARACTER(SEPERATOR, ParamArray args() As Variant) ' this function can take multiple parameters and return each cell value seperated by "+" Dim i, j Dim out For i = LBound(args) To UBound(args) If IsNull(args(i)) = False Then 'check null value If IsArray(args(i)) = False Then 'check if arg is array 'no array do usual If out = "" Then out = args(i) ElseIf args(i) = "" Then out = out Else out = out & SEPERATOR & args(i) End If Else ' arg is an array, do for array For j = 1 To args(i).Count If out = "" Then out = args(i)(j) ElseIf args(i)(j) = "" Then out = out Else out = out & SEPERATOR & args(i)(j) End If Next j End If ' array check end End If 'null check end Next i CONCATENATE_WITH_CUSTOM_CHARACTER = out End Function
