View Full Version : Solved: Concatenate via code..

Zack Barresse
07-20-2004, 08:30 AM

I'm wondering what the easiest way would be to Concatenate an unknown amount of textual cells into a single string seperated with a specified character?

For example:

A1 B1 C1 D1
Hi There It's Me Hiya! It's you!

I want to be able to say, I want to start in A1, the text may not end in D1, but can use in a IV1.end(xltoright) to find it, and have it turn out looking like this:

Hi There,It's Me,Hiya!,It's you!

Thanks! :)

Ken Wright
07-20-2004, 09:38 AM
Posted in the Excel groups by J E McGimpsey

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))
'Call as =MultiCat(A1:A5)
'you can add a delimiter if you wish:
' =MultiCat(A1:A5,",")

End Function


07-20-2004, 09:51 AM
Hi Zack,
for a sub try.

Sub CoCat()
Dim MyRange As Range, txt As String, Cel
Set MyRange = Intersect(Rows("1:1"), ActiveSheet.UsedRange)
For Each Cel In MyRange
txt = txt & Cel & ","
txt = Left(txt, Len(txt) - 1)
If ActiveCell.Row() = 1 Then MsgBox "Select a cell in another row": Exit Sub
ActiveCell.Formula = txt
End Sub

07-20-2004, 10:28 AM
On the delimiter I prefer "|". The reason is in names and address the usual seperater is a , ex Smith, John E. or China, TX 77777


Zack Barresse
07-20-2004, 10:34 AM
Thanks Ken, MD, Tommy! That's why I love this site!! Three of the most respected (imho) coder's I've ever seen, w/ absolutely great solutions!!

I ended up with a variation of MD's code. Altered for the personal need but not much different. Ken, I'll keep that little gem for my personal file. :)

Thank you! :vv

07-20-2004, 11:21 AM
Glad, to be!, of help, MD