# Break apart a single cell to create a mailing list

1. ## Break apart a single cell to create a mailing list

We have software for our medical office that generates a patient list. Unfortunately, the XLS document that is generated places the patients name and address into a single field. I need to break that single field/cell into LAST NAME, FIRST NAME, ADDRESS LINE 1, ADDRESS LINE 2, CITY, STATE, ZIP.

For example, the entire cell might read
Bob Smith
123 Main Street
Apt A
Mytown, NY 12345

Or possibly:
Bob Smith
123 Main Street
Mytown, NY 12345

I am completely at a loss how to do this. Any help is greatly appreciated.

2. ## Re: Break apart a single cell to create a mailing list

Try this formula

in B1 and pull formula to the right until you see blanks

=TRIM(MID(SUBSTITUTE(\$A1,CHAR(10),REPT(" ",255)),(COLUMNS(\$A:A)-1)*255+1,255))

 A B C D E 1 Bob Smith 123 Main Street Apt A Mytown, NY 12345 Bob Smith 123 Main Street Apt A Mytown, NY 12345 2 Bob Smith 123 Main Street Mytown, NY 12345 Bob Smith 123 Main Street Mytown, NY 12345

3. ## Re: Break apart a single cell to create a mailing list

Frist, select your column of addresses, and use Text to column, delimited, and select the other box, and hold down the ALT key as you type 0010 on the numeric keypad, then click OK.

Then, in cell E2, use

=IF(COUNTA(A2:D2)=3,"",C2)

and in F2, use

=IF(COUNTA(A2:D2)=3,C2,D2)

and copy down to match. Then copy E:F and paste special values, then delete columns C and D.

Then insert a new column B and use text to columns on A to split the first and last names. Then use Text to columns on the city state zip to split those, and maybe use formulas like those above to extract the state and zip and recombine the city names if they are multi-word names.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1