Consulting

Results 1 to 6 of 6

Thread: Solved: Concatenate via code..

  1. #1
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location

    Solved: Concatenate via code..

    Hello,

    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!

  2. #2
    MS Excel MVP VBAX Regular Ken Wright's Avatar
    Joined
    Jun 2004
    Posts
    17
    Location
    Posted in the Excel groups by J E McGimpsey

    [VBA]
    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
    [/VBA]

    Regards
    Ken................
    It's easier to beg forgiveness than ask permission

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Cocatenate

    Hi Zack,
    for a sub try.
    MD

    [VBA]
    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 & ","
    Next
    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
    [/VBA]

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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

    Later

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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!

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Glad, to be!, of help, MD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •