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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="width: 124, bgcolor: transparent"]Full Address

[/TD]
[TD="width: 87, bgcolor: transparent"]Address 1

[/TD]
[TD="width: 87, bgcolor: transparent"]Address 2

[/TD]
[TD="width: 86, bgcolor: transparent"]Address 3

[/TD]
[TD="width: 87, bgcolor: transparent"]Town

[/TD]
[TD="width: 94, bgcolor: transparent"]County
[/TD]
[TD="width: 74, bgcolor: transparent"]Postcode

[/TD]
[/TR]
[TR]
[TD="width: 124, bgcolor: transparent"]Example ind. Est
123 example road
Idea way

Exampleton

Exampleshire

EG12 3IE

[/TD]
[TD="width: 87, bgcolor: transparent"]Example ind. Est

[/TD]
[TD="width: 87, bgcolor: transparent"]123 example road


[/TD]
[TD="width: 86, bgcolor: transparent"]Idea way

[/TD]
[TD="width: 87, bgcolor: transparent"]Exampleton

[/TD]
[TD="width: 94, bgcolor: transparent"]Exampleshire

[/TD]
[TD="width: 74, bgcolor: transparent"]EG12 3IE

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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,"")