Mike Painter said:
If it is a really big list, second best is to clean it up yourself
as John says.
First is to buy a normalized address list from the Post Office or
some other source, sometimes mail order houses will process your
list for you. NOTE. "Normalized" is not a relational thing here but
what the post office does to make addresses (uniform.)
An Instr search for the first space in an address will usually get
you to the street name and you can use the mid function to pick it
off. x = Instr(YourAddress, " ")
StreetNumber = Left(Your Address, x)
In here you check to find out if x = 0 and take care of that.
You also make sure that Streetnumber is a number or PO or P.O or a
street without a number.
StreetName =Mid( YourAddress, X + 1)
or maybe not depending on what you got above.
Might as well keep the PO happy and convert to all upper case at the
same time.
Any attempt to parse the street name will almost surely run into
problems. In the OP's example many streets do not have numbers, so
finding the first space will leave you with "Street". As you
mentioned it would be possible to test for certain values first. For
instance: StreetNameOnly: IIf(Asc(Left([Street],1)) < 65, Right([Street],
_
Len([Street] - InStr([Street]," ")),
[Street])
Then there would be a problem if the listing is something like "5th
Avenue". Another test could be built whereby if the leftmost
character is a number and the characters to the left of the first
space are "st","nd","rd", or "th" (1st, 2nd, 3rd, 4th, etc.) do not
strip off any characters. But there are likely more exceptions
beyond these. I'm not sure about what the OP would do after buying
the list from the PO or whoever, but in terms of the existing data I
doubt there is anything more reliable than John's suggestion, with
which you expressed agreement, to separate the number from the street
name.