Dealing with Address1, Address2, Address3, City,....

[V8burble]

I have a mailing address that is currently stored in a number of Excel cells
EG:
A1 contains Address1
B1 contains Address2
C1 contains Address3
D1 contains City
E1 contains County
etc
etc

I would like to pull all of these in to one multi-line cell, to be viewed by the user, BUT my problem is that I want to ignore any blank line entries

By simply entering:
= A1 & CHAR(13) & B1 & CHAR(13) & C1 etc etc

works fine for fully populated addresses, but leaves blank lines if not all address lines are populated, which looks rather naf.

I thought about using IF statements, but they got very complicated very quickly.

Please help

[vandanavai]

Hello

Instead of Using CHAR, why can't u use simple formula as below

Please Login or Register  to view this content.

=A2&","&B2&","&C2&","&D2&","&E2

Please Login or Register  to view this content.

It works evenif some fields are blank

[V8burble]

Char(13) adds a carriage return, but "," only separates by a comma

Maybe an example will help get across what I'm trying to do:
Say that the address in question only has the elements Addr1, City, County, Country, Post code.

My formula would produce:
Addr1


City
County
Country
Post code

But what I would like to see is:
Addr1
City
County
Country
Post code

Does that explain?

Thanks

[vandanavai]

Hello

Can u attach Sample file

[V8burble]

Now solved thank you