Problem with Merging Data Cells with Empty Cells

[Te_Diddy]

Hello

I need to merge 8 cells that contain text into one cell separated by
“,”. I used the following formula to do so:

=E3&”, “&F3&”, “&G3&”, “&H3&”, “&I3&”, “&J3”, “&K3&”, “&L3&”

Works great, the problem is, if the cells are blank I get a cell tha
looks like: “, , , , ,”. How do I merge just the data, and if the cel
is blank to not display anything?

Thanks in advance for your help

 

[Andy B]

Hi

If you replace your formula with
=E3&REPT(", ",E3<>"")&F3&REPT(", ",F3<>"")&G3&REPT(", ",G3<>"")&H3&REPT(",
",H3<>"")&I3&REPT(", ",I3<>"")&J3&REPT(",",J3<>"")&K3&REPT(", ",K3<>"")&L3

 

[Dave Peterson]

If each of the cells does not have a space character in it:

=SUBSTITUTE(TRIM(E3&" "&F3&" "&G3&" "&H3&" "&I3&" "&J3&" "&K3&" "&L3)," ",", ")


for readability, each space is replace with an underscore:
=SUBSTITUTE(TRIM(E3&"_"&F3&"_"&G3&"_"&H3&"_"&I3&"_"&J3&"_"&K3&"_"&L3),"_",",_")

 

[Guest]

You could use third party tool to safety merge cells with with keeping all of
multiple data values and unmerge cells with automatic dividing multiple data
values into several cells. You could download this software from
http://www.addintools.com.
This tool merge cells and separate the column values with comma and separate
the row values with line feed.