Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()
PageSetupXL4M()
PrevSheet()
RandInt()
SampleNoReplace()
SumBold()
UnixToXLTime()

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Concatenate multiple ranges

Excel's CONCATENATE() function takes multiple arguments, but each cell must be listed separately. In addition, if you want to delimit the concatenation by, say, inserting a space between cell texts, you have to add a separate delimiter for each cell, e.g.:

  = CONCATENATE(A1 & " " & B1 & " " & C1)

The MultiCat() function allows you to use a more compact syntax:

  = MultiCat(A1:C1," ")

MultiCat concatenates the text from the cells, rather than their underlying values. If you have the number 1234 in a cell formatted as "00000", MultiCat will return "01234" while CONCATENATE will return 1234.

Put this in a regular code module.

  '*****************************************
  'Purpose: Concatenate all cells in a range
  'Inputs:  rRng - range to be concatenated
  '         sDelimiter - optional delimiter
  '            to insert between cell Texts
  'Returns: concatenated string
  '*****************************************
  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

Valid XHTML 1.1Valid CSSMade on a Macintosh

Quick Links

Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros

Where to put your code

About Event Macros

Merge Text from multiple cells/columns