Concatinate list of cells

[homegrownandy]

Im trying to convert a list of data into a block of text which I will then paste into a word document.

This formula will work, but I end up copying loads of blank cells/rows:

=C2 & CHAR(10) & CHAR(10)  & C3 & CHAR(10) &  CHAR(10) ... ect

Ive tried using this which fails when it hits a blank row:

=IF(ISBLANK(C2)=FALSE,C2 & CHAR(10) & CHAR(10),””) & IF(ISBLANK(C3)=FALSE,C3 & CHAR(10) & CHAR(10),””) & IF(ISBLANK(C4)=FALSE,C4 & CHAR(10) & CHAR(10),””)

Any idea how i can just copy the cells into a block of text without the blank cells?

 

[BrianGGG]

I think it was only introduced in Excel 2016, but have you looked at the TextJoin function?
It takes an array (in your case a list of cells) and joins them into a string. It even has a parameter to allow you to ignore empty cells.

Here's a list of rows on the left, along with the result of a TextJoin:

	B	C	D
one
two	one two three four
three
four

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]40[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]

[TD="align: right"][/TD]

[TD="align: center"]43[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]45[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D42[/TH]
[TD="align: left"]=TEXTJOIN(" ",TRUE,B40:B45)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If you don't have TextJoin the you could try this UDF

Function MyConcat(Rng As Range) As String
   Dim Cl As Range
   
   For Each Cl In Rng
      If Not Cl = "" Then
         If MyConcat = "" Then MyConcat = Cl & Chr(10) & Chr(10) Else MyConcat = MyConcat & Cl & Chr(10) & Chr(10)
      End If
   Next Cl
End Function

used like
=MyConcat(C2:C14)

 

[homegrownandy]

Thanks a lot both. Ill have a play and im sure ill figurre something out with this. Cheers!

Edit: both perfect!

 

[Fluff]

Glad we could help & thanks for the feedback