Hi
Modifying Gord's code as follows, should do what you want.
I have included the final solid pipe after the last cell value.
If you don't want it, then use Gord's final line.
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
Press Alt+F11 to enter the
VB Editor.
If you have a module within the workbook, copy and paste the function there.
If not, choose InsertModule then paste the code into the large white pane
of the new module.
The code will work with any range of cells.
If you are wanting 8 columns on a single line then on the spreadsheet type
=concatrange(A1:H1)
and copy down
--
Regards
Roger Govier
"Excel Helps" wrote in message
...
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.