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

[laufeyhiddles]

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
123 Canada Ltd.
123 Canada Road
Somewhere, CA A1B2C3
CANADA_____________

Thank you!

 

[FormR]

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)

 

[laufeyhiddles]

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

 

[FormR]

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.