Modify a UDF please?

[Excel Helps]

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

 

[ilia]

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?

 

[Excel Helps]

Hi Ilia
The input contains 3 cells for this example.
In fact the live file has up to 8 input cells.

So the output is: a¦a|b¦b|c¦c
From input: a b c

Thanks