Separating Addresses

  • Thread starter Thread starter Mike DeNuccio
  • Start date Start date
M

Mike DeNuccio

I have a database of addresses that are inputed like this:

100 East Sunshine Street
200 East Sunshine Street, #400
3456 W Jones
1 Jones, Apt 2A

I need to divide this Address field into thee parts: House Number; Street; Apt (only if it has an apartment which is indicated by the comma in the field)

I know how to separate the House Number from the Address field: Left([address],InStr([address]," "))
I know how to separate the Street from the Address if there is no apartment: Right([address],(Len([address])-(InStr(1,[address]," "))))
I know how to separate the Apartment from the Address: Trim(Right([address],(Len([address])-(InStr(1,[address],", ")))))

So here's my problem - if the Address has a apartment, I cannot get only the Street to appear. Basically, I need everything in between the first space and the comma to appear in a new field called Street. For example, in the second example above, it would divide into this:

House Number: 200
Street: East Sunshine Street
Apt: #400

Seems easy, but I simply cannot figure out why it won't work.

The formula I was using for addresses with Apts is: Trim(Mid([address],InStr([address]," ",InStr([address],", ")-1))) but this does not return anything. I did this formula because in English what I was trying to do was to say that the start character is the first space in the field. The end character is the one with the comma (in the format Mid([String], Start, End). Then I realized the formula is Mid(String, Start, Length).

So I tried this: Trim(Mid([address],InStr([address]," ",Len([address])-InStr([address],", ")-1)))

Any help would be appreciated! If I am doing a very long version for the ones I have, please correct those as well.
Thanks in advance. If possible, reply to me AND post.

Mike
 
I have a database of addresses that are inputed like this:

100 East Sunshine Street
200 East Sunshine Street, #400
3456 W Jones
1 Jones, Apt 2A

I need to divide this Address field into thee parts: House Number; Street;
Apt (only if it has an apartment which is indicated by the comma in the
field)

I know how to separate the House Number from the Address field:
Left([address],InStr([address]," "))
I know how to separate the Street from the Address if there is no apartment:
Right([address],(Len([address])-(InStr(1,[address]," "))))
I know how to separate the Apartment from the Address:
Trim(Right([address],(Len([address])-(InStr(1,[address],", ")))))

So here's my problem - if the Address has a apartment, I cannot get only the
Street to appear. Basically, I need everything in between the first space
and the comma to appear in a new field called Street. For example, in the
second example above, it would divide into this:

House Number: 200
Street: East Sunshine Street
Apt: #400

Seems easy, but I simply cannot figure out why it won't work.

The formula I was using for addresses with Apts is:
Trim(Mid([address],InStr([address]," ",InStr([address],", ")-1))) but this
does not return anything. I did this formula because in English what I was
trying to do was to say that the start character is the first space in the
field. The end character is the one with the comma (in the format
Mid([String], Start, End). Then I realized the formula is Mid(String,
Start, Length).

So I tried this: Trim(Mid([address],InStr([address],"
",Len([address])-InStr([address],", ")-1 ) ) )

Any help would be appreciated! If I am doing a very long version for the
ones I have, please correct those as well.
Thanks in advance. If possible, reply to me AND post.
#####################


Mike,

I struggled for ten or twenty minutes with all the various arguments and
(), with no luck; this late at night, it all looks the same.

Here it is encapsulated in VBA:

Public Function CaptureStreet(FullStreetAddress As String) As String

Dim StreetName As String
Dim FirstSpacePosition As Byte
Dim LengthToLastSpace As Byte
Dim ReversedFullStreetAddress As String
Dim ReversedFirstSpacePosition As Byte
Dim FullAddressLength As Byte

FirstSpacePosition = InStr(FullStreetAddress, " ")
ReversedFullStreetAddress = StrReverse(FullStreetAddress)
ReversedFirstSpacePosition = InStr(ReversedFullStreetAddress, " ")

FullAddressLength = Len(FullStreetAddress)
LengthToLastSpace = FullAddressLength - _
FirstSpacePosition - _
ReversedFirstSpacePosition

StreetName = Trim(Mid(FullStreetAddress, _
FirstSpacePosition, _
LengthToLastSpace))

CaptureStreet = StreetName

End Function

It worked for both the addresses shown above in the sample data, plus some
other basic combinations, like altering the length of the Street Number and
the Apartment Number, but that only amounted to five minutes testing, so it
may bear closer scrutiny.


Sincerely,

Chris O.
 
Back
Top