Excel VBA code to concatenate with custom characters

Sometimes it is necessary to join the contents of cell with custom characters like +,-,:,|, etc. This can be achieved 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 be displayed separated by “+” sign.

The code:

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