Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Concatenate Multiple Columns With Spaces

  1. #1
    Join Date
    22nd January 2008
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

    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.

  3. #3
    Join Date
    23rd April 2007
    Posts
    3,490
    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)
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    31st May 2008
    Posts
    19

    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/

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  6. #6
    Join Date
    24th May 2011
    Posts
    5

    Re: Concatenate Multiple Columns With Spaces

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2012
    Posts
    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..

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    23rd April 2007
    Posts
    3,490

    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," ")), " ", ",").

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th December 2012
    Posts
    7

    Re: Concatenate Multiple Columns With Spaces

    Quote 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, " ")," ")), " ", ",")....

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th December 2012
    Posts
    7

    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...

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Concatenate 2 Columns & Delete Orginal Columns
    By Rumple09 in forum EXCEL HELP
    Replies: 4
    Last Post: September 25th, 2007, 10:05
  2. Replies: 2
    Last Post: May 25th, 2007, 05:07
  3. Concatenate Two Columns
    By tis707 in forum EXCEL HELP
    Replies: 3
    Last Post: November 21st, 2006, 18:23
  4. Creating new columns using spaces
    By Derek11112 in forum EXCEL HELP
    Replies: 2
    Last Post: July 21st, 2006, 23:38
  5. Replacing multiple spaces with single spaces
    By cscribner in forum EXCEL HELP
    Replies: 3
    Last Post: February 22nd, 2006, 07:30

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
  •  
porno