Saturday, March 19, 2011

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!

No comments:

Post a Comment