This may be easy, but it is hard for me

[SofaKingWeTatDid]

The formatting doesn't display exactly the same on the forums, but essentially I have thousands of entries like this separated by blank rows. I want to put all of this information into a single row separating street, city state zip, and country. The biggest problem is the block of merged cells where the address info is. Unmerging the cells deletes data, text to columns doesn't seem to work. and I cant record a macro to copy by the line instead of by the cell.

I am frustrated and I dont want to do thousands of these by hand.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
6	VendorNumber						*	Name/Address																										*	*	*	*	*	*	Telephone															*	Extension
7							*																											*	*	*	*	*	*																*
8	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
9	0000002						*	City of Allentown																														*	*	610-437-7507															*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	Room 110435 Hamilton StreetAllentown, PA *18101-1685USA United States of America																														*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*																															*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*																															*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*																															*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet1

 

[SofaKingWeTatDid]

Bueller?