Separate Address

[lilgreen]

I have been tasked with making this excel file to have the name, street address, the city, and state separated from col a. The problem is there are no good commas to delineate. How would this be best worked? Below Is a sample of what I have. I know it is a mess. I am thinking the only way is to manually copy and paste. I think there are around 500 to do.

Name	Address Line 1	City	State
Beachhead Gatherings
918 Oakwood Dr Castle Rock CO
Colorado
Harvest Baptist Church
603 S Gilbert St Castle Rock CO
Colorado

 

[BSALV]

you can add an auxiliary column C, which calculates the number of spaces-1 in the street&City-line. If the city is more then 1 word, you have to manually substract 1 or 2 of that number, try it, you'll see, so if you had there a "5" change it in a "4" or a "3"
The formulas in columns C:E, you copy them down along with your data.

If your done with adapting column C and satisfied with the result, you can select the whole range C:E, copy it and paste as values in the same place or somewhere else.
Then you filter or ... to get rid of the empty rows

Map1
	A	B	C	D	E	F	G
1	Name		auxiiliary	name	Address Line 1	City	State
2			4	Beachhead Gatherings	918 Oakwood Dr Castle	Rock	Colorado
3	Beachhead Gatherings
4	918 Oakwood Dr Castle Rock CO
5	Colorado
6			5	Harvest Baptist Church	603 S Gilbert St Castle	Rock	Colorado
7	Harvest Baptist Church
8	603 S Gilbert St Castle Rock CO
9	Colorado
Blad1

Cell Formulas
Range	Formula
C2:C9	C2	=IF($A2="",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))-1,"")
D2:D9	D2	=IF($A2="",A3,"")
E2:E9	E2	=IF($A2="",TRIM(LEFT(SUBSTITUTE(A4," ",REPT(" ",100),C2),100)),"")
F2:F9	F2	=IF($A2="",MID(A4,LEN(E2)+2,LEN(A4)-LEN(E2)-3),"")
G2:G9	G2	=IF($A2="",A5,"")

 

[lilgreen]

you can add an auxiliary column C, which calculates the number of spaces-1 in the street&City-line. If the city is more then 1 word, you have to manually substract 1 or 2 of that number, try it, you'll see, so if you had there a "5" change it in a "4" or a "3"
The formulas in columns C:E, you copy them down along with your data.

If your done with adapting column C and satisfied with the result, you can select the whole range C:E, copy it and paste as values in the same place or somewhere else.
Then you filter or ... to get rid of the empty rows

Map1
	A	B	C	D	E	F	G
1	Name		auxiiliary	name	Address Line 1	City	State
2			4	Beachhead Gatherings	918 Oakwood Dr Castle	Rock	Colorado
3	Beachhead Gatherings
4	918 Oakwood Dr Castle Rock CO
5	Colorado
6			5	Harvest Baptist Church	603 S Gilbert St Castle	Rock	Colorado
7	Harvest Baptist Church
8	603 S Gilbert St Castle Rock CO
9	Colorado
Blad1

Cell Formulas
Range	Formula
C2:C9	C2	=IF($A2="",LEN(A4)-LEN(SUBSTITUTE(A4," ",""))-1,"")
D2:D9	D2	=IF($A2="",A3,"")
E2:E9	E2	=IF($A2="",TRIM(LEFT(SUBSTITUTE(A4," ",REPT(" ",100),C2),100)),"")
F2:F9	F2	=IF($A2="",MID(A4,LEN(E2)+2,LEN(A4)-LEN(E2)-3),"")
G2:G9	G2	=IF($A2="",A5,"")

While I appreciate the answer I think it is going to be easier to copy/paste. I will still have to do a lot of manual work. I think you did great but I doubt there is a solution.

 

[BSALV]

500 addresses ... with a little luck 1-2 hours ???
the problem is that there is too much difference in the addresses, number of words, etc