Concatenate question

[yovation]

I tried posting this yesterday but haven't seen it.

I am using this formula to concatenate.

=(A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1&", "&G1&", "&H1)

Some of the cells are blank. How can I change the formula so the
double commas ', , ' won't show.

Thank you.
David

 

[Guest]

=(A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1&", "&G1&", "&H1)

where you have A1&", "& change to

if(A1="","",A1&", ")

Do it for each similar segment in including H1 so H1 will also have a ", "

Let's call that the newformula (without the equal sign).

then do

=Left(newformula,len(newformula)-2)

If all the cells could be blank

=if(counta(A1:H1)=0,"",Left(newformula,len(newformula)-2))

 

[Guest]

assuming your formula is in column I, maybe try something like this in column
J:

=LEFT(SUBSTITUTE(I1, " ,", ""), LEN(SUBSTITUTE(I1, " ,", ""))-2)

 

[Ken]

David

Replace all the " , " with an if statement that checks the cell to the
left and puts a blank instead of " , " if the cell is blank. For
example:

=A1&IF(a1<>"",",","")&B1&IF(b1<>"",",","")&C1&IF(C1<>"",",","")&D1

Good luck.

Ken
Norfolk, Va

 

[yovation]

Thank you Tom and Vergel,

I tried the formula below and it works great.