Concatenation and skipping blank cells

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

Are all the values in A1:F1 single words--no spaces?

If yes:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ")



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

--

Dave Peterson

On Mon, 29 May 2006 12:29:01 -0700, soma104 <[email protected]>
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

On Mon, 29 May 2006 14:43:02 -0500, Dave Peterson <[email protected]>
wrote:

>Are all the values in A1:F1 single words--no spaces?
>
>If yes:
>
>=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ")
>
>

Neat solution for single word entries!


--ron

I think I'd use a UDF, too. But that formula could be modified for cells with
multiple words:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",CHAR(1))
&" "&SUBSTITUTE(B1," ",CHAR(1))
&" "&SUBSTITUTE(C1," ",CHAR(1))
&" "&SUBSTITUTE(D1," ",CHAR(1))
&" "&SUBSTITUTE(E1," ",CHAR(1))
&" "&SUBSTITUTE(F1," ",CHAR(1)))," ",", "),CHAR(1)," ")

But it gets kind of ugly kind of fast.

Ron Rosenfeld wrote:
>
> On Mon, 29 May 2006 14:43:02 -0500, Dave Peterson <[email protected]>
> wrote:
>
> >Are all the values in A1:F1 single words--no spaces?
> >
> >If yes:
> >
> >=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ")
> >
> >
>
> Neat solution for single word entries!
>
> --ron

--

Dave Peterson

On Mon, 29 May 2006 19:30:03 -0500, Dave Peterson <[email protected]>
wrote:

>I think I'd use a UDF, too. But that formula could be modified for cells with
>multiple words:
>
>=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(A1," ",CHAR(1))
> &" "&SUBSTITUTE(B1," ",CHAR(1))
> &" "&SUBSTITUTE(C1," ",CHAR(1))
> &" "&SUBSTITUTE(D1," ",CHAR(1))
> &" "&SUBSTITUTE(E1," ",CHAR(1))
> &" "&SUBSTITUTE(F1," ",CHAR(1)))," ",", "),CHAR(1)," ")
>
>But it gets kind of ugly kind of fast.
>

UDF's are certainly more flexible.
--ron

On Mon, 29 May 2006 15:50:40 -0400, Ron Rosenfeld <[email protected]>
wrote:

>On Mon, 29 May 2006 12:29:01 -0700, soma104 <[email protected]>
>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