Concatenate

[Lee]

Hi, Is there a way to concatenate multiple cells into 1
cell separated by a , without having to select each cell
using the usual concatenate function but by selecting a
range such as A1:A6.

A B
1 Text1 =CONCATENATE(A1,",",A2,",",A3,",",A4)
2 Text2
3 Text3
4 Text4
5 Text5
6 Text6

Thanks

 

[JE McGimpsey]

One way (using a UDF):

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, 2)
End Function


Call as

=MultiCat(A1:A4,",")

 

[Guest]

Thanks - don't mean to sound stupid but what is a UDF?
Never heard of it before?

 

[Bob Phillips]

Lee,

A UDF is a User Defined Function, that is a piece of VBA code that is used
in a similar manner to built-in Excel worksheet functions.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

Not stupid at all - I should have defined it:

User Defined Function

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

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

 

[Guest]

That works a treat - thanks for your help.

-----Original Message-----
Not stupid at all - I should have defined it:

User Defined Function

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

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


.