Concatenate formula
Results 1 to 8 of 8

Thread: Concatenate formula

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,590
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Concatenate formula

    I have a list of items down column B. The number of items in column B will vary from time to time.

    I am trying to concatenate each row in B (concatenate(B17","B18,".... I need them concatenated No spaces and separated by a ","

    My problem is how do I only concatenate down the list to where there is no more data. I don't want to write this extensive formula that will end up with a bunch of ,,,,,,,, at the end.

    I assume there is a way using VBA but I don't know how.

    My sheet name is "Input" My data starts in B17 and goes down to a variable row. I want to put the concatenated value in B2

    Any help us very much appreciated.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate formula

    What version of Xl do you have?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate formula

    Try this. I haven't tested it but it should get you in the ball park

    [Code]

    Option Explicit

    Sub Concat()

    Dim wsSheet1 as excel.worksheet
    Set wsSheet1 = Sheets("Sheet1")
    Dim rngTable1 as Range
    Dim rngB as Range
    Set rngTable1 = wsSheet1.Range("B17").CurrentRegion 'insert a line above row 17 so it doesn't grab data above it

    Dim rngRows as Long, rngRows1 as Long
    Set rngRows as rngTable1.Rows.Count
    Set rngRows1 as rngTable1.Rows(1).Count
    Dim i as long
    Dim conc as string
    Dim tconc as string

    For i = rngRows1 to rngRows
    conc = Range(i & "B").Value & ","
    tconc = tconc & conc
    Next

    End Sub

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate formula

    Quote Originally Posted by SupremeDr View Post
    Try this. I haven't tested it but it should get you in the ball park

    Code:
    Option Explicit
    
    Sub Concat()
    
    Dim wsSheet1 as excel.worksheet
    Set wsSheet1 = Sheets("Sheet1")
    Dim rngTable1 as Range
    Dim rngB as Range
    Set rngTable1 = wsSheet1.Range("B17").CurrentRegion 'insert a line above row 17 so it doesn't grab data above it
    
    Dim rngRows as Long, rngRows1 as Long
    Set rngRows as rngTable1.Rows.Count
    Set rngRows1 as rngTable1.Rows(1).Count
    Dim i as long
    Dim conc as string
    Dim tconc as string
    
    For i = rngRows1 to rngRows
    conc = Range(i & "B").Value & ","
    tconc = tconc & conc
    Next
    
    Range("B2").Value = tconc
    
    End Sub
    Sorry added the value to B2 here

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate formula

    If you have 365 or 2019 version you can use this formula
    =TEXTJOIN(",",1,B17:B1000)

    otherwise
    Code:
    Sub gheyman()
       Dim Cl As Range
       Dim St As String
       For Each Cl In Range("B17", Range("B" & Rows.Count).End(xlUp))
          If Not Cl = "" Then St = St & Cl & ","
       Next Cl
       Range("B2").Value = Left(St, Len(St) - 1)
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,590
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Concatenate formula

    2016 so TextJoin, didn't work. But code did work!
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Concatenate formula

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,097
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Concatenate formula

    Quote Originally Posted by gheyman View Post
    But code did work!
    If your data between cell B17 and the last data cell in Column B will never have any blank cells between those two cell limits (in other words, if all blank cells always occur after the last data item), then you can use this somewhat simpler code as well...
    Code:
    Sub gheyman2()
      Range("B2") = Join(Application.Transpose(Range("B17", Range("B17").End(xlDown))), ",")
    End Sub
    Last edited by Rick Rothstein; Aug 26th, 2019 at 03:39 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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