=TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(I2,",",J2 ,",",K2,",",L2,",",M2,",",N2,",",O2),",",",
")," ,",""))

If the range is completely empty or if the last cell that contains an entry
is any cell other than O2 that formula returns a trailing comma.

I2 = X

Formula returns X,

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
=TRIM(SUBSTITUTE(SUBSTITUTE(CONCATENATE(I2,",",J2, ",",K2,",",L2,",",M2,",",N2,",",O2),",",",
")," ,",""))


"Murph" wrote:

I apologize if this is a repeat.
I am trying to combine multiple columns with names in each into one
column
with ", " separating the values. Issue being blank cells still returning
", "
between info

i.e. -
Jonathan, Douglas, David, , , ,
Joseph, , , , , ,
Samantha Nicole, Mathew Robert, , , , ,
, , , , , ,
Paige, Payton, , , , ,

Naturally I'd like to get rid of the extra comma's and spaces. Thoughts?

Formula currently used:
=I2&", "&J2&", "&K2&", "&L2&", "&M2&", "&N2&", "&O2