Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Concatenate Multiple Columns With Spaces

1. I agreed to these rules
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. ## 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. 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. I agreed to these rules
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. ## Re: Concatenate Multiple Columns With Spaces

Not too often I make members laugh

6. Join Date
24th May 2011
Posts
5

## Re: Concatenate Multiple Columns With Spaces

Originally Posted by Dave Hawley
=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".

Excel Video Tutorials / Excel Dashboards Reports

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

## Re: Concatenate Multiple Columns With Spaces

Originally Posted by mikerickson
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. 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...

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

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

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