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 for this info!

1 comment:

  1. An interesting dialogue is price comment. I feel that it is best to write more on this matter, it may not be a taboo topic however usually individuals are not enough to talk on such topics. To the next. Cheers.
    excel courses