Concatenate text while moving to the next line in the cell

[ben.biddle]

I have addresses spread across multiple columns in a table so that
there is a column Addr1, Addr2, City, State, and Zip. I want to
combine the text in each cell into one cell, but I want it to appear in
the cell over more than one line, like it would on an envelope, rather
than all on one line. I tried recording a macro to see what I might
need to insert into my function to make it work (the macro records
Chr(10)) but this results in an error. Anyone know what character I
have to use, presuming it's even possible?

 

[Max]

Perhaps this might also do it ..

Assuming data in cols B to F, from row2 down

Put in G2:
=IF(COUNTBLANK(B2:F2)=5,"",B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2&CHAR(10)&F2)

Format G2 to wrap text via clicking:
Format > Cells > Alignment tab > check "Wrap Text" > OK

Then just copy G2 down as far as required