Concatenating multiple cells in Excel

Ever want to do the equivalent of ",".join(['foo', 'bar', 'baby'] in Excel?

One way is use the Excel formula:

=A1 & ", " & A2 & ", " & A3...

One other cool way is to add this Visual Basic function. Inside View->Macros, you can add this one:

Public Function MultiCat( _
        ByRef rRng As Excel.Range, _
        Optional ByVal sDelim As String = "") _
             As String
     Dim rCell As Range
     For Each rCell In rRng
         MultiCat = MultiCat & sDelim & rCell.Text
     Next rCell
     MultiCat = Mid(MultiCat, Len(sDelim) + 1)
  End Function

Then you can use:

= MultiCat(A1:C1," ")

Thanks to http://www.mcgimpsey.com/excel/udfs/multicat.html for this info!