Google-sheets – How to TEXTJOIN in Google Sheets

concatenateformulasgoogle sheetsregex

I have ten columns in my rows which contain 5 sets of first name/last name. I want to combine each of these names in a final column using TEXTJOIN (or whatever function is appropriate if I'm missing something).

I tried this: 

=TEXTJOIN(", ", true, W2 & " " & X2,Y2 & " " & Z2,AA2 & " " & AB2,AC2 & " " & AD2,AE2 & " " & AF2)

But if I don't have some names in the 2nd or 3rd or 4th or 5th sets, then it will come back with Jerry Jackson, , , ,

I'd like to do the conditionals and add the commas where appropriate, but all of my modifications include the commas.

Please help.

Best Answer

=REGEXREPLACE(TRIM(SUBSTITUTE(JOIN(", ", 
 W2&" "&X2,Y2&" "&Z2,AA2&" "&AB2,AC2&" "&AD2,AE2&" "&AF2), 
 " ,", "")), "^,|,$", "")

0

Related Topic