I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up with
some values separated by commas, empty spaces and more commas without more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

Do any of cells you want to concatenate *already* contain commas and/or
spaces?

--
Biff
Microsoft Excel MVP


"Jorge E. Jaramillo" wrote in
message ...
I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and
that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up
with
some values separated by commas, empty spaces and more commas without more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

It's messy, but you could change your
=a1&", "&a2&", "&a3&", "&a4
along the lines of
=IF(A1="","",a1&", ")&IF(A2="","",a2&", ")&IF(A3="","",a3&", ")&a4
--
David Biddulph

"Jorge E. Jaramillo" wrote in
message ...
I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and
that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up
with
some values separated by commas, empty spaces and more commas without more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

If the last cells are empty that leaves a comma at the end of the string.

x, y,

Try this: (all on one line):

=SUBSTITUTE(TRIM(A1&" "&A2&" "
&A3&" "&A4)," ",", ")

x, y

However, if the cells already contain spaces and/or commas the result won't
look right.

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It's messy, but you could change your
=a1&", "&a2&", "&a3&", "&a4
along the lines of
=IF(A1="","",a1&", ")&IF(A2="","",a2&", ")&IF(A3="","",a3&", ")&a4
--
David Biddulph

"Jorge E. Jaramillo" wrote in
message ...
I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and
that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up
with
some values separated by commas, empty spaces and more commas without
more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

Or without formulas:
Copy/PasteSpecial the cells you want to concatenate into Word as
"Unformatted Text"
Find/Replace. Check "use wildcards".
Find what: ^t{1,}
Replace with: ,
Or export the concatenated string (with multiple commas) into Word.
Find what: ,{1,}
Replace with: ,
Copy/Paste back into Excel

On Sat, 1 Nov 2008 21:26:00 -0700, Jorge E. Jaramillo
wrote:

I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up with
some values separated by commas, empty spaces and more commas without more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

Here's one method.

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

Then use this formula to concatenate, for example, A1:A5 :

=IF(ISERR(FIND(", ",SETV(MCONCAT(A1:A5,", ")),
LEN(GETV())-1)),GETV(),LEFT(GETV(),LEN(GETV())-2))


The total length of the string must be less than 256 characters. If it is
longer, a VBA routine could be used.
--ron

Here's another method using the Morefunc add-in.

Array entered** :

=SUBSTITUTE(TRIM(MCONCAT(IF(
A1:A5<"",A1:A5,"")&" "))," ",", ")

The same caveat applies, the resulting string must be less than 256
characters.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Sat, 1 Nov 2008 21:26:00 -0700, Jorge E. Jaramillo
wrote:

I know that to join the contents of several cells into a single one
(concatenate is the technical word), all it takes is the function & and
that
to have values separated by a comma and a space all you have to do is:
a1&", "&a2&", "&a3&", "&a4

My problem is that sometimes some of these cells are empty so I end up
with
some values separated by commas, empty spaces and more commas without more
values in between.

What would be the way to concatenate only the non-empty cells?

Thanks

Here's one method.

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

Then use this formula to concatenate, for example, A1:A5 :

=IF(ISERR(FIND(", ",SETV(MCONCAT(A1:A5,", ")),
LEN(GETV())-1)),GETV(),LEFT(GETV(),LEN(GETV())-2))


The total length of the string must be less than 256 characters. If it is
longer, a VBA routine could be used.
--ron

Thank you for the answers. I am going to try them. Just so you know, cells
content is only either empty or short text string without spaces or commas.