| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Preparing an address book...
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preparing an address book...
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. " wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preparing an address book...
On Oct 29, 2:13*pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) &
"hotmail.com" wrote: 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. " wrote: 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- Hide quoted text - - 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Preparing an address book...
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... " wrote: On Oct 29, 2:13 pm, Sheeloo <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote: 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. " wrote: 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- Hide quoted text - - 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| e-mail address via outlook address book | Excel Programming | |||
| Address Book | Excel Discussion (Misc queries) | |||
| .xls to OE address book | Excel Discussion (Misc queries) | |||
| How do I import Office address book to Outlook Express address bo. | Excel Discussion (Misc queries) | |||
| How to insert an address from Outlook 2003 address book ? | Excel Programming | |||
Linear Mode
