Combine two or more cells in Excel

[saimanikumar]

Hi,

I would like to combine 2 or more cells with a break-point.

For example if
A1= Apple
B1 = Boy
C1= (Empty cell)
D1= Dog

I want the text to show in my new cell as
Apple
Boy
Dog

Any suggestions will be helpful!

Thanks in advance.
Sam

 

[patel45]

Sub b()
Set Rng = Range("A1:D1")
For Each Cell In Rng
  If Cell <> "" Then s = s & Cell & Chr(10)
Next
s = Left(s, Len(s) - 1)
Range("A2").Value = s
End Sub

 

[saimanikumar]

Hey Patel45,

Thank you so much for the reply. I am trying to write as a formula... here is what i found out so far
=CONCATENATE(B1,CHAR(10),C1,CHAR(10),D1) and then wrap the text
only problem with this is.. if there is an empty box i will get the output as

Apple
Boy

Dog

Any suggestions on how to remove the empty line??

Thanks again,
Saimani

Sub b()
Set Rng = Range("A1:D1")
For Each Cell In Rng
  If Cell <> "" Then s = s & Cell & Chr(10)
Next
s = Left(s, Len(s) - 1)
Range("A2").Value = s
End Sub

 

[shg]

=concatenate(
if(len(b1), b1 & char(10), ""),
if(len(c1), c1 & char(10), ""),
if(len(d1), d1 & char(10), ""))