Well, let's give it a similar function. See attached where cells H2:H5 contain variants of the formula (just to demonstrate the differences when you supply different argument values - note that cell D3 is blank and cell C4 contains a zero).
The user-defined function has been stolen from
https://www.ozgrid.com/forum/forum/h...325#post823325 and this could be tweaked to simplify its use for you.
For information the code is:
Code:
Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer)
' Build a single string from a passed range with a passed separator
' placed between each value. The separtaor can be more than 1 character
' If Blank = 0, cells = "" are ignored, else they are concatenated
' If Zero = 0, cells = 0 and <> "" are ignored, else they are concatenated
Dim strAll As String, cel As Range, celCount As Integer
strAll = ""
celCount = 0
For Each cel In Parts.Cells
If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then
Else
celCount = celCount + 1
strAll = strAll & Separator & cel.Value
' Removes separator placed before first concatenated cell
If celCount = 1 Then
strAll = Right(strAll, Len(strAll) - Len(Separator))
End If
End If
Next cel
ConcatenateRange = strAll
End Function