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

=(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(newform ula)-2))

--
Regards,
Tom Ogilvy


"yovation" wrote:

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

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

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


--
Hope that helps.

Vergel Adriano


"yovation" wrote:

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

Thank you Tom and Vergel,

I tried the formula below and it works great.

On Jul 17, 12:10 pm, Vergel Adriano
wrote:
assuming your formula is in column I, maybe try something like this in column
J:

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

--
Hope that helps.

Vergel Adriano

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(C 1<"",",","")&D1

Good luck.

Ken
Norfolk, Va



On Jul 17, 12:39 pm, yovation wrote:
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