On Mon, 29 May 2006 15:50:40 -0400, Ron Rosenfeld
wrote:

On Mon, 29 May 2006 12:29:01 -0700, soma104
wrote:

I'm trying to set up a formula which will contactenate the contents of 6 or 7
cells. I would like to be able to skip any blank values.

Using the following formula: =a1&", "&b1&", "c1&", "&d1&", "&e1&", "&f1
Assuming cell b1, d1, and e1 were left blank, I would get the following:
Ann, , Cathy, , , Frank

What I want to see is: Ann, Cathy, Frank
Can anyone give me any ideas on how to go about this?

Soma104

You could download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr.

Then use the formula:

=MCONCAT(A1:A6,", ")

OR you could use a UDF. <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Use the formula:

=ConcatNonBlanks(A1:A6,", ")

===========================
Function ConcatNonBlanks(rg As Range, Optional Separator As String) As String
Dim c As Range

For Each c In rg
If Len(c.Text) 0 Then
ConcatNonBlanks = ConcatNonBlanks & c.Text & Separator
End If
Next c

'remove last separator
If Not IsEmpty(Separator) Then
ConcatNonBlanks = Left(ConcatNonBlanks, _
Len(ConcatNonBlanks) - Len(Separator))
End If

End Function
==============================


--ron

Some testing reveals that we can eliminate testing for the use of Separator, so
the routine simplifies a bit:

============================
Function ConcatNonBlanks(rg As Range, Optional Separator As String) As String
Dim c As Range

For Each c In rg
If Len(c.Text) 0 Then
ConcatNonBlanks = ConcatNonBlanks & c.Text & Separator
End If
Next c

'remove last separator
ConcatNonBlanks = Left(ConcatNonBlanks, _
Len(ConcatNonBlanks) - Len(Separator))

End Function
========================
--ron