Break apart a single cell to create a mailing list

**mooresc** · 09-17-2014, 09:59 AM

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.

**AlKey** · 09-17-2014, 10:11 AM

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

**Bernie Deitrick** · 09-17-2014, 11:35 AM

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.