This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
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
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Not familiar with User Defined Functions (UDFs)? See David McRitchie's Getting Started With Macros
About Event Macros