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

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

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

"ilia" wrote:

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