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.

Date< SHOULD READ DATA
SORRY!

"Excel Helps" wrote:

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.

Hi

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

--

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.

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( B2<"","¦","")
&B2&IF(B2<"","|","")&C2&IF(C2<"","¦","")&C2&IF( C2<"","|","")

--

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.

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.

Hi Gord

I don't think that will work.
the OP is using two different forms of pipe between the text
red¦red|white¦white|blue¦blue|


--

Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

Thank you both for your very welcome advice.
I have just re-sent my query as the broken pipes were missing from the last
couple of posts. The actual product file that I have has 8 columns of
choices, per product that will display as a dropdown menu.
If this can be done by a UDF it would save a long keyboard session, I am new
to UDF'S although I have created the CSV output file from my product list
using a macro so I guess I could manage it with a little help.
In the meantime I will try Roger's method.
Thank you both, from a keen novice!

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&"|"


"Roger Govier" wrote:

Hi Gord

I don't think that will work.
the OP is using two different forms of pipe between the text
redred|whitewhite|blueblue|


--

Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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(B2 <"","","")
&B2&IF(B2<"","|","")&C2&IF(C2<"","","")&C2&IF(C2 <"","|","")

--

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


redred|whitewhite|blueblue|
redred||blueblue|
|whitewhite||
|||

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.

Thanks Roger.

These old eyes did not pick up the difference in the piping.

Gord

On Sun, 20 Jan 2008 10:38:10 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

Hi Gord

I don't think that will work.
the OP is using two different forms of pipe between the text
red¦red|white¦white|blue¦blue|