Strip numbers in a field

[JMM]

I have a street field and want to be able to strip the number and leave the street name only. eg. 124 Smith Sreet would become Smith Street. I can't use Left$ because the number preceding the street name could be 1 character or 6.
Any ideas appreciated

 

[scking]

Try this but check the syntax for the Left$ and Mid$ functions. This is on-the-fly on-the-road.

Steve King

Public Function StripNumber(strAddress As String) As String

Dim intLen As Integer
Dim intCnt As Integer
intLen = Len(strAddress)
For intCnt = 1 to intLen
If IsNumeric(Left$(1, strAddress)) _
Or Left$(1, strAddress)=" " Then
strAddress = Mid$(2, strAddress)
Else
Exit For
End If
Next intCtr
StripNumber = strAddress
End Function



Growth follows a healthy professional curiosity

 

[MichaelRed]

The folowing will do your 'example'. Please NOTE the 'issue' with items which are just SLIGHTLY different from the example. I expect you need a LOT of thought in defining the rules for what you are REALLY looking for here.

Public Function StripNumber(strAddress As String) As String

    'To return the NON-Numeric bytes in a string
    'Usage:
    '? StripNumber("124 Smith Sreet")
    'Smith Sreet

    '? StripNumber("124A Smith Sreet")
    'A Smith Sreet

    Dim intCnt As Integer
    Dim MyChr As String * 1
    Dim MyAddr As String

    For intCnt = 1 To Len(strAddress)

        MyChr = Mid(strAddress, intCnt, 1)
        If (Not IsNumeric(MyChr)) Then
            MyAddr = MyAddr & MyChr
        End If
  
    Next intCnt

StripNumber = Trim(MyAddr)

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Just thought I'd weigh in with one more option. Ah variety! ;-)

Function StripNumbers(sStr As String) As String
Dim i As Long
For i = 0 To 9
sStr = Replace(sStr, CStr(i), "", 1, -1, vbTextCompare)
Next i
StripNumbers = Trim(Replace(sStr, " ", " ", 1, -1, vbTextCompare))
End Function

?StripNumbers("124 Smith Sreet&quot
Smith Sreet

?StripNumbers("1281 N Main St #123&quot
N Main St #
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[JMM]

Thanks for your responses. MichaelRed, you are spot on with your code. It works of course; and I am stuck with text that follows a number ie 44a Smith Street. Don't know how to strip the 'a'. Can the space between the number and street name be taken into account?
Any further ideas?

 

[MichaelRed]

Yor need to carefully work through not JUST this specific example, but a whole suite of 'badly formed' variations. Terry's second example has one more varariety, but there are many more. What about [Apt | Suite | 2nd Floor | P.O. Box | ... ]. The real issue here is that the exception processing will be orders of magnitude more complex than the 'normal' routine. Overall, this is in the same category as seperating "whole names" into their component elements, where it is necrssary to deal with formal titles, salutations, Familial lineage, 'ranks', degrees and variations in spelling and capitalization rules.
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over