Easily concatenate several cells?

[Kenneth Lam]

Other than using:
=concatenate(A1,A2,A3,A4,A5)
or
=A1&A1&A3&A4&A5
or
VBA code

is there any way to do concatenate like
=XXXX(A1:A5) ?

I have tried using concatenate in array but doesn't work.
Thanks

 

[Peo Sjoblom]

Download Morefunc and use the MCONCAT function

 

[Kenneth Lam]

Any way to do it using internal method? This add-in is not default in
all Excel. I can't send this to someone else and ask them to install
this add-in in order to view my xls file.

 

[JE McGimpsey]

One way:

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

Call as =MultiCat(A1:A5)

you can add a delimiter if you wish:

=MultiCat(A1:A5,",")

If you're not familiar with UDFs, see David McRitchie's "Getting Started
with Macro and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Frank Kabel]

Hi
only alternative would be to create a similar user defined function on
your own. No build-in worksheet function available for this

 

[Kenneth Lam]

Hi
only alternative would be to create a similar user defined function on
your own. No build-in worksheet function available for this

I see. Thanks.

 

[Kenneth Lam]

On Fri, 30 Apr 2004 22:30:06 -0600, JE McGimpsey

Thanks. The reason why I don't want to user VBA is because if someone
use my xls and choose not to active the macro inside, then my written
function will be inactive.

Thanks anyway.

Best Regards.

 

[Frank Kabel]

Hi Kenneth
if I require macros for my spreadsheets and Iwant to 'force' the user
to activate these macros I do the following:
- Create a 'welcome sheet' which explains why to enable macros is
required and that without macros the file won't work
- hide all sheets and protect the workbook
- in the workbook_open event unprotect the workbook, unhide all sheets
and hide the welcome sheets. This way the welcome sheet would only be
displayed if you disable macros (as in this case the unprotection and
unhiding won't work)
- in the workbook_close event hide all sheets and unhide the welcome
sheet

 

[Kenneth Lam]

On Sun, 2 May 2004 20:53:31 +0200, "Frank Kabel"

Thanks a lot for your suggestion. It is very good.