Find beginning of alpha characters in a string

[Steve Hoiness]

I have a table full of addresses.

I would like return the right most part of the address
excluding any left numbers or spaces.

Can this be done?

Example
Have
1234 Elm
13 Elm

Want
Elm
Elm

 

[Rick Brandt]

I have a table full of addresses.

I would like return the right most part of the address
excluding any left numbers or spaces.

Can this be done?

Example
Have
1234 Elm
13 Elm

Want
Elm
Elm

Function JustTheStreet(strIn As String) As String

Dim i As Integer

i = Len(strIn)

Do Until i = 0
If IsNumeric(Mid(strIn, i, 1)) = False Then JustTheStreet= Mid(strIn, i, 1)
& JustTheStreet
i = i - 1
Loop

JustTheStreet= Trim(JustTheStreet)

End Function

 

[John Spencer (MVP)]

Trouble is there are addresses in the States that look like:

10024 1/2 South Baltimore Avenue or

1024 3rd Avenue

This is not an easy problem to solve. I would probably attempt to do this with
a VBA function.

Not that dificult once you get your head around it.
Basically the IIF, Left, Len and IsNumeric functions will
do this for you, I'm guessing that you want to use this
in a query of some kind (rather than on a form) but let me
know if it is for a form because it can also be done in
VBA. Right, Here it goes....
(I assume that your address field is called [address].

IIF(IsNumeric(Left([Address],1),IIF(IsNumeric(Left
([Address],2),IFF(IsNumeric(Left([Address],3),IFF
(IsNumeric(Left([Address],4),Right([Address,Len([Address]-
4)),Right([Address,Len([Address]-2)),Right([Address,Len
([Address]-1)),Address)

I know it looks nasty, but really it's not. all it
actually does is look at the first four charicters in turn
and check alpha or numeric, then take the right hand end
of the string for the length of the string minus how far
in it gets. If you get this going ok, I would also
consider encasing the right commands in a trim statement
to remove any extra spaces.

My only warning is that I wote this here rather than in
access so the commas may be wrong places. If you use the
expression builder it's all point and click, so you should
get it fairly quickly.

Oh, and by the way, I assume you are in the states, so
I've gone in for four numbers if you are in the UK (Like
me) I really wouldnt bother going past three

Si.

-----Original Message-----
I have a table full of addresses.

I would like return the right most part of the address
excluding any left numbers or spaces.

Can this be done?

Example
Have
1234 Elm
13 Elm

Want
Elm
Elm
.