Remove comma from concatenated string for last cell in Excel

1
votes

I have a table in Excel that has 10 columns. For each row there are different numbers of cells populated e.g.

enter image description here

My concatenated string is as follows for HTML emails

(" + Machine1 + ", " + Machine2 + ", " + Machine3 + ", " + Machine4 + ", " + Machine5 + ", " + Machine6 + ")

However I want the comma to not show after the last cell in the row (whether that be column 3,4,5,6).

This is what I get

(TEST1, TEST2, TEST3 , TEST4, TEST5, , )

I want to remove the two commas at the end. Hope this makes sense!

2
excelvba

2 Answers

2
votes

If you have Office 365 Excel then use TEXTJOIN:

="(" & TEXTJOIN(", ",TRUE,A2:F2) & ")"

If not then you will need to use IF for each return and Mid:

=MID("(" & IF(A2 <> "",", " & A2,"") & IF(A2 <> "",", " & B2,"") & IF(C2 <> "",", " & C2,"") & IF(D2 <> "",", " & D2,"") & IF(E2 <> "",", " & E2,"") & IF(F2 <> "",", " & F2,""),3,999)
1
votes

Alternative without IF

Just in addition to Scott's valid solution, I demonstrate an approach via the REPT function. Applied on a cell containing a string, it "repeats" its content & comma once (indicated by COUNTA() equalling 1), whereas an empty cell results in a zero repetition which allows to omit not only the cell content but the comma, too: 

="(" & SUBSTITUTE(REPT(A1&",",COUNTA(A1))&REPT(B1&",",COUNTA(B1))&REPT(C1&",",COUNTA(C1))&REPT(D1&",",COUNTA(D1))&REPT(E1&",",COUNTA(E1))&REPT(F1&",",COUNTA(F1))&"$",",$",")")

A simple SUBSTITUTE removes the last comma, wherever it occurs before the closing bracket ")".