parsing out an undelimited address and city

[kenlyn]

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]

With all the variation of addresses, the only reliable way that I know is to do
it manually.

Good luck.

 

[kenlyn]

With all the variation of addresses, the only reliable way that I know is to do
it manually.

Good luck.













--

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]

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.

 

[kenlyn]

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.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave:

Thanks.

My goal is the FIX the easy ones and have a manual human eye on the
difficult ones.

This should help.

We have placed a request for the client to export the data in a better
format - but they don't think they can.

Have a great weekend.

You bring me value.


Kenlyn

 

[Dave Peterson]

Clients are like that.

Maybe it's time to raise your rates for that client <vbg>.

The real challenge may be when they send you the updated list--still in that
miserable format!