Parsing City, State, Phone Numbers etc

[iainmunro]

Evening

I have a list of addresses that are different lengths etc and have tried different ways to separate out the date with Left, Right, Mid, Text to Columns etc, but don't seem to be getting anywhere.

Here is a sample:
"4834 E. Lincoln Street
Wichita, KS 67218
(316) 684-0290
Classics, Episode DV0101

Williams' Creek Depot - FM 470
Tarpley, TX 78883
(830) 562-3250
www.macandernies.com
Classics, Episode DV0101

3132 East Magnolia Avenue
Knoxville, TN 37914
(865) 524-4388
That's Italian, Episode DV0102

Any ideas as to the best way to get each piece into a column ?

Iain

 

[hiker95]

iainmunro,

Thanks for the new workbook.

Macros ReorgDataV2, and ReorgDataV3, are looking to split your raw data on worksheet DinersDrive-insDives (per your original posted workbook on Box.Net) in column D.


Screenshots from your latest workbook:

Excel Workbook
	A	B
1	10th Ave Burrito	817 Belmar PlazaBelmar, NJ 07719(732) 280-1515www.tenthaveburrito.comWings 'n Things, Episode DV1012
DinersDrive-insDives.csv

Excel Workbook
	A
1	72112 Baker BlvdBaker, CA(760) 733-4354Classics, Episode DV0101
Sheet1

What worksheet do you want the macro to work on?

 

[hiker95]

iainmunro,

The below is a screenshot of what worksheet DinersDrive-insDives.csv should look like when we split column B.


But, where is the YELLOW cell data coming from?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	10th Ave Burrito	817 Belmar PlazaBelmar, NJ 07719(732) 280-1515www.tenthaveburrito.comWings 'n Things, Episode DV1012	817 Belmar Plaza	Belmar	NJ	07719	(732) 280-1515	40.1804776	-74.0266689	www.tenthaveburrito.com	Wings 'n Things, Episode DV1012	Diners Drive-ins and Dives
DinersDrive-insDives.csv