combining multiple columns using ", " seperations..

[Murph]

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

 

[Dave Peterson]

If there are no cells that have spaces in them (like "Jonathon Smith"), then you
could use:

=substitute(trim(i2&" "&j2&" "&k2&" "&l2&" "&m2&" "&n2&" "&o2)," ",", ")

Or you may want to use JE McGimpsey's UDF called MultiCat:
http://mcgimpsey.com/excel/udfs/multicat.html

With a minor tweak:

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng.Cells
if isempty(rcell.value) then
'skip it
else
MultiCat = MultiCat & sDelim & rCell.Text
end if
Next rCell
if multicat <> "" then
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
end if
End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=multicat(i22,", ")

 

[T. Valko]

=I2&", "&J2&", "&K2&", "&L2&", "&M2&", "&N2&", "&O2

As long as none of the referenced cells contain spaces and/or commas...

All on one line...

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

 

[Teethless mama]

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

 

[T. Valko]

=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,