Announcement

Collapse
No announcement yet.

Concatenate Multiple Columns With Spaces

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Concatenate Multiple Columns With Spaces



    Hi,

    I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.

    I've found solutions that say use

    =concatenate(A1, " ", B1)

    or the VBA equivalent

    I have at least 15-20 columns which I wish to concatenate.


    Is there an easier way than to type for all 15 columns?

    =concatenate(A1," ", B1," ", C1,....)


    Also, the number of columns of each row vary

    Thank you very much

  • #2
    Re: Concatenate Multiple Columns With Spaces

    =trim(a1 & " " & B1 & " " &.....& T1)

    Or, use the CONCANTENATE function with you mouse pointer.

    BTW, your thread would have taken longer to write than typing 20 cell references.

    Comment


    • #3
      The way I'd use this UDF for what you need is
      =ConcatIf(A1:O1,"<>",A1:O1," ")

      The UDF ConcatIf mirrors the syntax of SumIf

      =ConcatIf(compareRange,criteria,[stringsRange],[delimiter],[noDuplicates])

      =ConcatIf(compareRange,"<>") will return a string of all the cells in compareRange.

      Example:
      ColA ColB
      w 1
      x 2
      y 1
      z 1
      x 3
      y 1
      x 2
      t 2

      ConcatIf ($A:$A,"x",$B:$B,",")= "2,3,2"
      ConcatIf ($A:$A,"x",$B:$B,",",TRUE)= "2,3"
      ConcatIf ($B:$B,1,$A:$A)="wyzy"
      ConcatIf ($A:$A,"<>")=""wxyzxyxt" (i.e. all of them)
      Code:
      Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                                  Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
          Dim i As Long, j As Long
          With compareRange.Parent
          Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
           End With
          If compareRange Is Nothing Then Exit Function
          If stringsRange Is Nothing Then Set stringsRange = compareRange
          Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                                      stringsRange.Column - compareRange.Column)
          
              For i = 1 To compareRange.Rows.Count
                  For j = 1 To compareRange.Columns.Count
                      If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                          If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                              ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                          End If
                      End If
                  Next j
              Next i
              ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
          End Function

      Comment


      • #4
        Re: Concatenate Multiple Columns With Spaces

        BTW, your thread would have taken longer to write than typing 20 cell references.
        Dave's post actually made me laugh out loud. Which is rare for a Dave Hawley post (for obvious rules-related reasons!) Very good point though Dave, I thought this upon reading the original post. I was expecting you needing 1000s done, but 15-20 can be done in a text editor in 10 minutes than copied over.


        Keep up the good work,
        ryan
        http://www.yanaboo.com/

        Comment


        • #5
          Re: Concatenate Multiple Columns With Spaces

          Not too often I make members laugh

          Comment


          • #6
            Re: Concatenate Multiple Columns With Spaces

            Originally posted by Dave Hawley View Post
            =trim(a1 & " " & B1 & " " &.....& T1)

            Or, use the CONCANTENATE function with you mouse pointer.

            BTW, your thread would have taken longer to write than typing 20 cell references.
            Well I had similar problem as "SalicBlu3" and I looked for the answer as I was using .xls file and it was not allowing me to use concatenate more than a certain times.
            Also I have to use this formula for multiple sheets on different columns (total number around 15 to 20).
            "Mr. mikerickson" code is amazing. This is exactly what I required, although it was not necessary to have "If" condition as I could have used the extra spaces using "ASAP Utilities".
            Thank you Mr. Mikerickson for your code. It is really helpful.

            Comment


            • #7
              Re: Concatenate Multiple Columns With Spaces

              Hi Mikerickson,
              I am new to this forum and to these codes....I tried your code which is working excellent for me....
              but I don't want to get the delimiter (comma or space) if the cells in "B" column are blank when the criteria in coulmn "A" is satisfying,
              for example,
              a
              b 1
              a
              b 2
              in this case when my criteria is 'a', I should not get the result like two commas (",,"), I want to get only blank cell

              and I want to run this function only on visible cells (means similar to the working of "subtotal" function..

              suggestions in this regard may be highly helpful

              Thank you..

              Comment


              • #8
                Re: Concatenate Multiple Columns With Spaces

                if you want a comma delimiter, but ignore blank results, try =SUBSTITUTE(TRIM(ConcatIf(A:A,"a",B:B," ")), " ", ",").

                Comment


                • #9
                  Re: Concatenate Multiple Columns With Spaces

                  Originally posted by mikerickson View Post
                  if you want a comma delimiter, but ignore blank results, try =SUBSTITUTE(TRIM(ConcatIf(A:A,"a",B:B," ")), " ", ",").
                  this is working fine to avoid the blank cells and having comma delimiter, but to run this only on visible cells, I tried combining concatrange function with concatif function.
                  it's not working ...It is showing some error of #value!
                  I tried SUBSTITUTE(TRIM(ConcatIf(sheet1!A:A,"a",concatrange(sheet1!B:B, " ")," ")), " ", ",")....

                  Comment


                  • #10
                    Re: Concatenate Multiple Columns With Spaces

                    Hi...
                    This is my excel sheet (attached), sheet1 consists of data that will be updated on day to day and in sheet2 I compiled the same data in a report form using concatif function,
                    here I want to run the concatif() function in sheet2 on the visble cells only.
                    i.e. if I filter my data by "Subject" or "date from" or "date to", I want the report only on filtered or visible cells only..i.e subject wise or month wise...


                    please help.....I am new to this macro thing...

                    oh sorry....file attachment option is not working now...will attach it as soon as the attachment option starts working...

                    Comment


                    • #11
                      Re: Concatenate Multiple Columns With Spaces

                      Please do not post questions in threads started by other members.

                      Start your own thread, give it an accurate and concise title that summarises your issue and explain your problem fully. If you think this thread can help clarify your question, you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

                      Comment


                      • #12
                        Re: Concatenate Multiple Columns With Spaces

                        Hi,

                        There is a way write this as a VBA loop that would paste the values in a specified range? I currently am using this but it takes a long time to calculate for each row.

                        Dim ThirdParty As String
                        ThirdParty = "=ConcatIf('All Filings'!$B$15:$B$" & lr & ",C10,'All Filings'!$O$15:$O$" & lr & ",""" & ", """ & ",TRUE)"
                        Sheet16.Range("D10").FormulaArray = ThirdParty
                        Sheet16.Range("D10:D" & GM).FillDown
                        Sheet16.Range("D10:D" & GM).Copy
                        Sheet16.Range("D10:D" & GM).PasteSpecial xlPasteValues
                        Application.CutCopyMode = False

                        Comment


                        • #13


                          Re: Concatenate Multiple Columns With Spaces

                          @rshelby.
                          Please read the rules again. You must have forgotten #3. You can get to the rules by clicking on "these rules" which is part of the "I agreed to these rules." sentence under your moniker.
                          And, start your own post as this one is very old. You can refer to this one if you think it will help.

                          Comment

                          Working...
                          X