Joining the contents of several cells into a single one

[Jorge E. Jaramillo]

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

 

[T. Valko]

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

 

[T. Valko]

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.

 

[Herbert Seidenberg]

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

 

[Ron Rosenfeld]

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

 

[T. Valko]

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)

 

[Jorge E. Jaramillo]

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.