Concatenate with carriage return- how to remove blanks?

[cindyc]

I'm trying to concatenate 15+ columns into one and have the results appear in
list format- so I've used =concatenate(a1,char(10),a2,char(10),a3.....
function. Wrap text is enabled. However, some source columns contain blanks
and I don't want a blank row showing (it will make my row height huge!) and
make the list look odd with big blank spaces. Any ideas?

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm
then use this formula

=SUBSTITUTE(MCONCAT(IF(A1:A15<>"",A1:A15&CHAR(10),"")),CHAR(10),"",COUNTA(A1:A15))

ctrl+shift+enter, not just enter

 

[Héctor Miguel]

hi, cindy !

I'm trying to concatenate 15+ columns into one and have the results appear in list format-
so I've used =concatenate(a1,char(10),a2,char(10),a3..... function. Wrap text is enabled.
However, some source columns contain blanks and I don't want a blank row showing
(it will make my row height huge!) and make the list look odd with big blank spaces. Any ideas?

you might want to give a try defining your own UDF (in a standard code module) like the following:

Function ConcatenateNonBlanks(Data As Range, _
Optional byColumns As Boolean = False, _
Optional Separator As String = ", ") As String
Dim resValues As Variant
resValues = Evaluate("if(" & Data.Address & "<>""""," & Data.Address & ",""|"")")
With Application
ConcatenateNonBlanks = .Substitute(.Substitute( _
Join(IIf(byColumns, .Transpose(.Transpose(resValues)), .Transpose(resValues)), _
Separator), "|" & Separator, ""), Separator & "|", "")
End With
End Function

then, you can use (i.e.) [A1] cell to put your "separator" character (i.e.) =char(10) as the 3rd argument
passing as true/<non cero> the second (byColumns) to horizontal concatenate or leave it blank to vertical concatenation
and call the above sample function (i.e.)

=ConcatenateNonBlanks(B1:L1,1,A1)
=ConcatenateNonBlanks(B1:B15,,A1)

hth,
hector.

 

[Gord Dibben]

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Ignores blank cells.

Usage is =concatrange(A1:A17)


Gord Dibben MS Excel MVP