Consulting

Results 1 to 6 of 6

Thread: Concatenate via code..

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

    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


    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

    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


    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

  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,940
    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
  •