Combine two or more columns by using a function

[Guest]

Hello,

I have combine 3 columns using the concatenate function and used CHAR(10) to
insert a new line for each column. Here's my example:

A1: 100 Main Street
B1: Suite 12
C1: Washington, DC 20016
D1: ?

So in D1, I created a function =Concatenate(A1, CHAR(10), B1, CHAR(10), C1)
and the result is perfect!

100 Main Street
Suite 12
Washington, DC 20016

My problem is when B1 column doesn't have any data, I want Excel to
eliminate the blank line. How can I get it to work? I'm lost at this point

Please help,
Thanks!

 

[Guest]

try this:

=IF(OR(B1="",B1=0),CONCATENATE(A1,CHAR(10),C1),CONCATENATE(A1, CHAR(10), B1,
CHAR(10), C1))

 

[Guest]

Thank you so much Nikki!

try this:

=IF(OR(B1="",B1=0),CONCATENATE(A1,CHAR(10),C1),CONCATENATE(A1, CHAR(10), B1,
CHAR(10), C1))