Preparing an address book...

[ucanalways]

Hi all,

I have list of addresses in an excel sheet.

Cell A1 has Mr/Mrs
Cell B1 has First name
Cell C1 has Last name
Cell D1 has the address1
Cell E1 has the address2
Cell F1 has the City
Cell G1 has State
Cell H1 has the Zip code.

likewise A2 thru H2... for 289 rows (A289 thru H289)........


Example: Mr. Robert Hamstick AAAAA BBBBBB
Charlotte NC 28205

Expected result:

Mr. Robert Hamstick
AAAAA
BBBBBB
Charlotte
NC - 28205

Sometimes, the value in address 2 is not present. In this case, the
result would be
Mr. Robert Hamstick
AAAAA
Charlotte
NC - 28205

and not
Mr. Robert Hamstick
AAAAA

Charlotte
NC - 28205

How would I get the expected result? Please let me know...
Any help or references to online material to solve this topic would be
greatly appreciated.

Thanks, Kevin

 

[Sheeloo]

If you want the result in once cell then enter this where you want the result
=A1&" "&B1&"
"&C1&CHAR(10)&D1&IF(E1="","",CHAR(10)&E1)&F1&CHAR(10)&G1&CHAR(10)&H1 and copy
down

This will suppress the line for Add2. You will have to format the cells as
'Text Wrap'
checked.

 

[ucanalways]

If you want the result in once cell then enter this where you want the result
=A1&" "&B1&"
"&C1&CHAR(10)&D1&IF(E1="","",CHAR(10)&E1)&F1&CHAR(10)&G1&CHAR(10)&H1 and copy
down

This will suppress the line for Add2. You will have to format the cells as
'Text Wrap'
checked.
















- Show quoted text -

Hi Sheeloo, that formula lists the address in a single row. I would
like to have it in the format I specified in the initial post

Like the following:
Mr. Robert Hamstick
AAAAA
Charlotte
NC - 28205

and not as Mr. Robert Hamstick AAAAA Charlotte NC - 28205

Please let me know how would I achieve the expected result.. Thanks

 

[Sheeloo]

Is it a one time activity? If yes then copy the resultant column to word and
copy back...
or paste to notepad, remove all " and paste back to Excel...