Important question. Does your input range contain three cells, with
values a, b, c - or does it contain six cells with values a, a, b, b,
c, c?


On Jan 22, 7:11*pm, Excel Helps
wrote:
Could this be slightly modified please:

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
Dim pipe As Boolean
pipe = True
* * For Each Cell In CellBlock
* * pipe = Not pipe

* * * * If Len(Cell.Text) 0 Then
* * * * * * If pipe = False Then
* * * * * * sbuf = sbuf & Cell.Text & Chr(166)
* * * * * * Else
* * * * * * sbuf = sbuf & Cell.Text & "|"
* * * * * * End If
* * * * End If
* * Next
* * ConCatRange = sbuf
End Function

To achieve this
a¦a|b¦b|c¦c
rather than
a¦b|c¦

Thanks Guys