Concatenate with carriage return- how to remove blanks?

C

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?
 
H

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top