Address Consolidation

[Sezebear]

Hi,

Can somebody please help me I am having a mindblank.

I am trying to show an address in one Cell (Full Address). Theaddress is currently spread over 6 columns. example below


Thank you in advance

Full Address	Address 1	Address 2	Address 3	Town	County	Postcode
Example ind. Est 123 example road Idea way Exampleton Exampleshire EG12 3IE	Example ind. Est	123 example road	Idea way	Exampleton	Exampleshire	EG12 3IE

<tbody>
</tbody>

 

[Yongle]

In A2 copied down
=B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2&CHAR(10)&F2&CHAR(10)&G2

 

[Yongle]

Same formula, amended to avoid empty rows if any of the cells contains no value

=IF(B2>"",B2,"")&
IF(C2>"",CHAR(10)&C2,"")&
IF(D2>"",CHAR(10)&D2,"")&
IF(E2>"",CHAR(10)&E2,"")&
IF(F2>"",CHAR(10)&F2,"")&
IF(G2>"",CHAR(10)&G2,"")