Breakout House and Streetname from address field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.

I need help to breakup a mailing address. I'm hoping to use Left( or Right(
in a query. Here are two examples:

1022 TURN CIRCLE SE #180
PMB269
SEATTLE, WA 98401

This is all one field with hard returns after 180 and PMB269.

7041 N TERRIFIC ST
SEATTLE WA 98401

This one has a hard return after ST and again after 98401.

Both of these examples are in the same column. I can get the house # in its
own field, but don't know how to get street name.

Can anyone help? Thank you!!!!!
 
You can find the carriage return using Chr(13). Your expression will be
something like this (assuming it's in a query):
StreetName: Mid$([address],InStr(1,[address],"
"),InStr(1,[address],Chr(13))-InStr(1,[address]," "))

Barry
 
Hi Maria,

It's difficult and very, very tedious to do this sort of thing with the
standard VBA string functions. You can get vastly more power and
flexibiity by using regular expressions.

There's a sample VBA function here
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm
that uses a regular expression to parse the "street" line of many US
addresses into its components. On that page and this one
http://www.j.nurick.dial.pipex.com/Code/index.htm
there are more functions and links to information on using regular
expressions with VBA.
 
Back
Top