ExcelBanter - parsing out an undelimited address and city

On 1 Feb 2007 13:02:36 -0800, 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

You may be able to limit it somewhat by using Regular Expressions to define
things.

For example, you could define the "city" as a terminal string of NON-DIGITs
that are preceded by a variable number of spaces which is then preceded by an
optional period and then by

a digit
PKY
AVE
CT
ST
RD

etc.

This, however, would fail on addresses such as:

4139 W HAVEN MORTON

BIRCH POINT PERRY

But maybe it'll help.

To use this, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

Address:
=REGEX.SUBSTITUTE($A1,"([\S\s]+(\d|ave|ct|st|rd))\.?\s+(\D+$)","[1]",,,FALSE)

City:
=REGEX.SUBSTITUTE($A1,"([\S\s]+(\d|ave|ct|st|rd))\.?\s+(\D+$)","[3]",,,FALSE)

------------------------------------------

Another approach, which might be better, would be to have a list of cities some
place.

Then use these formulas:

City: =MID(A1,MAX(IF(ISNUMBER(SEARCH(CityList,A1)),SEARC H(CityList,A1),0)),255)

The above must be entered as an **array-formula**. Hold down <ctrl<shift
while hitting <enter. Excel will place braces {...} around the formula.

Address: =TRIM(SUBSTITUTE(A1,City,""))

(City is the adjacent cell reference where the city is "printed".

--ron