# How to also exclude a line break in a formula when certain word is excluded

#### laufeyhiddles

##### New Member
I am currently creating a concatenate formula to take information from certain cells to create an address block. In my formula I exclude the word USA if that is entered in the country cell, but does not exclude CANADA when it is entered in the country cell (IF(K62="USA","",K62)). Issue is shown below. When my formula excludes the word USA, it still keeps a blank area where USA would be when I wrap text. I am wondering how do I get that to stop so the cell box no longer has a blank area.

=CONCATENATE(E62," ",CHAR(10),"",IF(F62="","",F62&" "&CHAR(10)),"",G62," ",CHAR(10),"",H62,", ",I62," ",J62," ",CHAR(10),"",IF(K62="USA","",K62))

John Smith
123 United States, LLC
123 United States Road
Anywhere, ST 12345
____________________ --- Area is kept blank. Underscore represents edge of cell. ---
Joanna Smith
Somewhere, CA A1B2C3

Thank you!

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi, it's a little tricky to to untangle you current formula, but does this alternative work any better:

=E62&CHAR(10)&IF(F62="","",F62&CHAR(10))&G62&CHAR(10)&H62&", "&I62&" "&J62&IF(K62="USA","",CHAR(10)&K62)

That worked! Could you explain to me why we changed most of the quotes and commas to "&" symbols?

Great

Could you explain to me why we changed most of the quotes and commas to "&" symbols?

There are a couple of way to concatenate values in Excel, one is the function CONCATENATE() and another is using the & operator - you were using a mixture of both, I just elected to do away with the function call and do it all the same way.

Last edited:

Replies
2
Views
305
Replies
1
Views
718
Replies
0
Views
180
Replies
0
Views
143
Replies
3
Views
330

1,196,057
Messages
6,013,165
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back