Combining Cell Contents (Part 2)

[PaolaAndrea]

Hello,

Thank you for your previous reply, it was great.

One more variable than I didn't think about, if cell B2 is empty how can I
make sure that only A2 is populated and that I don't have a blank line on the
bottom?

Example:

A2 = 109 Vine Hill & B2 = (Empty)

Result:
109 Vine Hill

NOT:
109 Vine Hill
(Empty Line)

Thank you.

Sincerely,

PaolaAndrea

 

[Dave Peterson]

Check the other suggestions at your original post.

 

[Dave Peterson]

First, it's good to respond in the same thread.

Second, since you had multiple responses, you'll want to share the formula you
used--and what happened when you tried it.

 

[Gord Dibben]

Use this UDF

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

If any cell is blank, no extra linefeed is entered.

=ConCatRange(A2:F2) or whatever range you choose.


Gord Dibben MS Excel MVP