You may be able to do stuff based on the number of strings in the cell--but I
still think it's gonna be a long and tedious effort.

Say your data is in C1:Cxxx
And then put this in B1:
=trim(c1)
And put this in A1:
=LEN(B1)-LEN(SUBSTITUTE(B1," ",""))+1

Now you can sort your data by column A.

Maybe you'll be able to see a pattern--select the range with 3 elements and
extract that data.

Then look at the items with 4, then 5.

You may be able to use some of David McRitchie's suggestions, too.

It ain't gonna be easy.


wrote:

On Feb 1, 4:25 pm, Dave Peterson wrote:
With all the variation of addresses, the only reliable way that I know is to do
it manually.

Good luck.





wrote:

I am trying to write programing to parse out a data file to seperate
the address and the city.

Examples below:

1149 N CARRIER PKY APT A44 GARDEN PRAIRIE
306 ROYALTY AVE WALHALLA
40 DANAS CT APT 1 WESTBRIDE
4139 W HAVEN AVE MORTON

The results I want are below:

Address City
1149 N CARRIER PKY APT A44 GARDEN PRAIRIE
306 ROYALTY AVE WALHALLA
40 DANAS CT APT 1 WESTBRIDE
4139 W HAVEN AVE MORTON

I have tried text to columns with delimited by spaces, but the problem
is you don't know how many spaces there are?

Any help?

Thanks

KTG

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave - thank you for your answer. I have always found your answers to
be helpful.

McRitchie - suggests replacing common road extensions with " St ",
with " St, " then delimit by the comma - this is great except for
things like

1215 S Main St St John MN .... i guess I could check if there where
more than 3 columns of data and have a manual look up of that. .....

Your thoughts.

Thanks,

Kenlyn

--

Dave Peterson