' MyConCat
' =MyConCat(" - ",A1:A15)
' or =MyConCat(,A5:B23)
'
' Very simple By Apostolos Goulandris
'
Function MyConCat(myDelimiter As String, Avar As Range) As String
Dim b As Variant, Dum As String

If IsMissing(myDelimiter) Then myDelimiter = ""
For Each b In Avar
    Dum = IIf(Len(b) > 0, Dum & myDelimiter & b, Dum)
Next
MyConCat = IIf(Len(myDelimiter) > 0, Mid(Dum, Len(myDelimiter) + 1, Len(Dum)), Dum)

End Function

' Concatenate multiple items...
'
' =CONCAT(A1:A12)
' =CONCAT(A1:A12," - ")
'
' by HansV at http://www.eileenslounge.com/viewtopic.php?f=27&t=6270
'
Function ConCatA(rng As Range, Optional sep As String = ",") As String
  Dim rngCell As Range
  Dim strResult As String
  For Each rngCell In rng
    If rngCell.Value <> "" Then
      strResult = strResult & sep & rngCell.Value
    End If
  Next rngCell
  If strResult <> "" Then
    strResult = Mid(strResult, Len(sep) + 1)
  End If
  ConCatA = strResult
End Function

' Concatenate multiple items in multiple ranges
'
' =ConCat("",A1:A3,C1,"HELLO",D1:D2)
' or like this (note the leading comma)...
' =ConCat(,A1:A3,C1,"HELLO",D1:D2)
'
' By Rick Rothstein at http://www.eileenslounge.com/viewtopic.php f=27&t=6270
'
Function ConCatB(Delimiter As Variant, ParamArray CellRanges() As Variant) As String

    Dim Cell As Range, Area As Variant

    If IsMissing(Delimiter) Then Delimiter = ""

    For Each Area In CellRanges
        If TypeName(Area) = "Range" Then
            For Each Cell In Area
                If Len(Cell.Value) Then ConCatB = ConCatB & Delimiter & Cell.Value
            Next
        Else
            ConCatB = ConCatB & Delimiter & Area
        End If
    Next

    ConCatB = Mid(ConCatB, Len(Delimiter) + 1)
End Function