Hi,
The array2string macro below should do the job for you. I wrote it a long time ago when I was learning about Excel arrays and have used it constantly since then. By chance, its latest use was to to do exactly what you want. Assuming that your values are in A3:C3, the formula would be ...
{=PERSONAL.xls!Array2String(""""&A3:C3&""""," | ")}
Please note that array2string always produces a trailing delimiter. If this causes you a problem then, assuming that the formula is in D3, put the following in E3 ...
=mid(D3,1,len(D3)-3)
'############################################################################################################################
Function Array2String(xArray As Variant, Optional xDelim As String = ",", Optional xNoBlanks As Boolean = False) As String
'Return an array as an (optionally delimited)string, optionally skipping Blanks.
'e.g. =Personal.Xlsb!Array2String({"A","B","C"},"--", False)
'e.g. =IF($A2=$A1,"",Personal.Xls!array2string(IF(($A2:$A31=$A2)*(C2:C31<>0)*(C2:C31<>""),C2:C31,""),CHAR(10),TRUE))}
Dim xHold As String
Dim xValue As Variant
For Each xValue In xArray
If (Not xNoBlanks Or CStr(xValue) <> "") Then xHold = xHold & CStr(xValue) & xDelim
Next
Array2String = xHold
End Function
'############################################################################################################################
The function has worked fine for me in Excel 97, 2003 and 2007. Hope it solves your problem,
Regards,
BrianR