After concatenateing need to get rid of last comma

We have 8 columns. The amount of cells with data in it varies anywhere from one cell to all 8 cells. We would like to combine the data in all 8 with a comma and space in between each one but no commas for those cells that don't have data. We also would like to add an "&" before the last one (replacing the comma).

We used this VBA to add the commas to the end of each individual cell:

Please Login or Register  to view this content.

We then did concatenate for all 8 cells which pulled it all together into one. We are stuck at how to get rid of the last comma (at the end) and how to replace the second to last comma with the &.

Right now it comes out something like this:

Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7, Data 8,

We would like it:

Data 1, Data 2, Data 3, Data 4, Data 5, Data 6, Data 7 & Data 8

We would appreciate help with this

Hi and welcome to the forum

Thanks for the title change

If your data ia in A1, try this in B1...
=SUBSTITUTE(LEFT(A1,LEN(A1)-1),","," &",LEN(A1)-LEN(SUBSTITUTE(A1,", "," ")))

OK use this instead...
=IFERROR(SUBSTITUTE(LEFT(A2,LEN(A2)-1),","," &",LEN(A2)-LEN(SUBSTITUTE(A2,", "," "))),A2)

If that still does not work, can you post a sample of when it doesnt work please?

Try this

Please Login or Register  to view this content.

=SUBSTITUTE(SUBSTITUTE(LEFT(A1,LEN(A1)-1),",","&",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))-1),", , ",", ")
try this

Thanks for the file

Your profile indicated 2010, but your sample file indicated 2003 or lower (.xls)

For 207 and later, use this...
=IFERROR(SUBSTITUTE(LEFT(L2,LEN(L2)-1),","," &",LEN(L2)-LEN(SUBSTITUTE(L2,", "," "))),LEFT(L2,LEN(L2)-1))

for 2003 or earlier, use this...
=IF(ISERROR(SUBSTITUTE(LEFT(L2,LEN(L2)-1),","," &",LEN(L2)-LEN(SUBSTITUTE(L2,", "," ")))),LEFT(L2,LEN(L2)-1),SUBSTITUTE(LEFT(L2,LEN(L2)-1),","," &",LEN(L2)-LEN(SUBSTITUTE(L2,", "," "))))

=IF((LEN(L2)-LEN(SUBSTITUTE(L2,",","")))=1,LEFT(L2,LEN(L2)-1),SUBSTITUTE(SUBSTITUTE(LEFT(L2,LEN(L2)-1),","," &",LEN(L2)-LEN(SUBSTITUTE(L2,",",""))-1),", , ",", "))
try this