+ Reply to Thread
Results 1 to 2 of 2

Concatenating a variable number of cells

  1. #1
    Registered User
    Join Date
    01-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Concatenating a variable number of cells

    First time poster I'm afraid!

    I'm looking to concatenate a number of cells of text together, the number of cells is variable and always followed by a blank cell - e.g.
    A B C
    item 1 text 1
    text 2
    text 3

    item 2 text 1
    text 2

    item 3 text 1

    etc.
    I'd like to concentate text 1,2,3 for each item into a cell in C - any thoughts?

  2. #2
    Registered User
    Join Date
    01-11-2012
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Talking Re: Concatenating a variable number of cells

    I think I've answered my own query, with the amendment of a solution elsewhere

    Public Function ConcateRange(InputRange, Optional delim As String = "/") As String

    Dim arr As Variant
    Dim Output As String
    Dim i As Long

    arr = InputRange
    For i = 1 To InputRange.Rows.Count
    If arr(i, 1) <> "" Then Output = Output & delim & arr(i, 1) Else Exit For

    Next i
    ConcateRange = Right(Output, Len(Output) - Len(delim))

    End Function

    the call used is =Concaterange(Gn:G999,", ") where n is the first line

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1