To save some typing you could try this UDF, which ignores blank cells and adds
the pipe only when there is data.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & "|"
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=concatrange(A1:C10)


Gord Dibben MS Excel MVP

On Sat, 19 Jan 2008 09:25:01 -0800, Excel Helps
wrote:

Thanks Roger,
I can now improve my file, I had to concede and put N/A where I really
needed a blank cell.
Have a good weekend.

"Roger Govier" wrote:

Hi

Try
=A2&IF(A2<"","¦","")&A2&IF(A2<"","|","")&B2&IF(B 2<"","¦","")
&B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF(C 2<"","|","")

--

Regards
Roger Govier

"Excel Helps" wrote in message
...
I have a list of products that have different options, that will be
displayed
on a website with various dropdown menus.
The menus are created from a CSV Import file that is itself created from a
large excel file simaler to this example:

RED WHITE BLUE
red white blue
red blue
white


red¦red|white¦white|blue¦blue|
red¦red|¦|blue¦blue|
¦|white¦white|¦|
¦|¦|¦|

The function to achieve the seperated pipes is:
=A2&"¦"&A2&"|"&B2&"¦"&B2&"|"&C2&"¦"&C2&"|"
My question is, what do I need to add to the function to NOT DISPLAY THE
PIPES WHEN THE CELL CONTAINS NO DATE.
At the moment, with my function, even if the cell contains no data I still
have the Pipes displayed which I don't want, I need just empty cells.