Instr and Numbers 1

[lizarde03]

How do you search for the first occurance of a number? I have very inconsistent data. However, it always begins with alphanumerics. I tried using something like
InStr([# 1 Alphanumeric Address]![ADDRESS1], Like "*[0-9]*&quot but I can't seem to get my syntax correct. Any suggestions?

 

[SteveR77]

If you are looking for the numeric part of an address for example 535 Main Street. You can use the Val function on the address field and it will extract the numeric part of the address for you. However, the order must be like the example given. If the data is West 535 North Main the Val function will not give you the numeric part of the string.

So Val([Address]) appears to be what you are looking for.

HTH,

Steve

 

[lizarde03]

Unfortunately, the string always begins with a letter..Any other ideas?

 

[SteveR77]

Could you provide a sample of the data?

 

[lizarde03]

Sure..

CARRERO ADA24-77

 

[SteveR77]

Thanks, but if I understood correctly the numeric part of the string could be any where after the first character.

Correct?

What I am trying to do is determine if there is a pattern to the data that can be used or if every character of the string would have to be tested for being a numeric value or not.


Steve

 

[lizarde03]

The numbers do come after a text string..however, there is no particular pattern to go by..

ASV.134 ABc
ASV35 93BCD
I'm trying to parse these out and i know i'm going to have to use several steps..

 

[MichaelRed]

while this doesn't seem like exactly waht you want, it should be along the lines of it and be easily modifiable to suit your specific purpose.

Public Function basNumsOnly(ByVal strIn As String) As Variant

    'Michael Red    2/15/04
    'To return some numeric parts of a string

    'Example Usage:
    '? basNumsOnly("wlerkjtl!@#.445")
    '.445

    '? basNumsOnly("wlerkjtl!@#$12.123")
    '$12.123

    Dim MyVal As Variant
    Dim MyChr As String * 1
    Dim Idx As Integer
    Dim blnDcml As Boolean
    Dim blnCurr As Boolean

    Idx = 1
    While Idx <= Len(strIn)
        MyChr = Mid(strIn, Idx, 1)


        If (MyChr = &quot;.&quot;) Then
            If (blnDcml = False) Then
                blnDcml = True
                MyVal = MyVal & MyChr
            End If
        End If

        If (MyChr = &quot;$&quot;) Then
            If (blnCurr = False) Then
                blnCurr = True
                MyVal = MyVal & MyChr
            End If
        End If

        If (IsNumeric(MyChr)) Then
            MyVal = MyVal & MyChr
        End If

        Idx = Idx + 1
    Wend

    basNumsOnly = MyVal

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SteveR77]

Michael,

Thanks for posting the code. It was what I had in mind but I was attempting to see if there was a pattern to make creating the code easier.

So, star for you Michael.