efandango,

I believe that it is impossible for you to have the Stree field.

If the format of your data is as consistant as the examples you posted, then
you could creat a "Street" field from the existing data by using a custom
function and use that function in an update query after the data is imported
to update a new field for the Stree only.

Here is a function that I tested against the examples you posted and in
every case it returned only the street, leaving off the address.  This would
allow you to have a street field by using this function is an update query.

'*****Start of Code*****
Public Function GetStreetOnly(CurAddress)
Dim bytChrLoc As Byte
Dim strPrevChar As String

'check for a space in the address
bytChrLoc = InStr(1, CurAddress, " ")
'if there is a space the "bytChrLoc" variable
'holdss the loction of the first space
If bytChrLoc > 0 Then
   'use the location of the space to check the
   'first character to the left of the space
   strPrevChar = Mid(CurAddress, bytChrLoc - 1, 1)
   If IsNumeric(strPrevChar) Then
       'if this character is numeric then is it
       'assumed that this is the Address
       GetStreetOnly = Right(CurAddress, Len(CurAddress) - bytChrLoc)
   Else
       'if this character is not numeric then
       'it is part of the Street
       GetStreetOnly = CurAddress
   End If
Else
   'if there is no space in the address then
   'the street is all that is there
   GetStreetOnly = CurAddress
End If
End Function
'*****End Of Code*****

The function can be called by simply passing in the entire address.