Extracting a Number from a string

[iheartvba]

I am trying to extract numbers from a string in the access query builder, all I need is the position of the numbers so the instr function will do. However I need a code like InStr([tblImportedIFor m].[description],"[0-9]") where ] "[0-9]" can be any number from 0 - 9. I can't figure out what to use as a wild card for numbers.


Please help

Thank You

[DonRayner]

You can't use wildcards in the InStr() function. It will treat wildcards as just another character.

[Stewart Ross]

Hi iheartvba. Instr is not suitable for this task. Your example will try to find the first occurrence of the string pattern "[0-9]" in your target string, which is not at all what you need.

There is no VBA string function I know of that returns the position of the first numeric character in a string. It is not difficult to write a custom function to do so, but to assist you further could you provide an example showing the format of the numbers within the string? Are they whole numbers, decimal values, or what? Are they in variable positions within the string? Are they contiguous values, or what?

As an example of what can be done, the function below simply returns a sub string containing all the numeric characters in the main string. Decimal points are ignored in this one.

Public Function fExtractNumeric(strInput) As String
    ' Returns the numeric characters within a string in
    ' sequence in which they are found within the string
    Dim strResult As String, strCh As String
    Dim intI As Integer
    If Not IsNull(strInput) Then
        For intI = 1 To Len(strInput)
            strCh = Mid(strInput, intI, 1)
            Select Case strCh
                Case "0" To "9"
                    strResult = strResult & strCh
                Case Else
            End Select
        Next intI
    End If
    fExtractNumeric = strResult
End Function

Example input: "abc def g123456hijk lmn"
Output: "123456"

-Stewart

[OldBirdman]

This isn't addressing the original question. iheartvba wants the POSITION of the numeric portion within [description], not the value.
Line 11 of the code suggested could be changed to:

fExtractNumeric = intI 
Exit Function

This would meet his conditions. However, I believe he was thinking that if "abc8de" Like "*[0123456789]*" is True, how could this be combined with some function (and InStr was only a suggestion) to return the position of the character 8 and not simply its existence.
I don't have an answer.

OldBirdman

[Stewart Ross]

Indeed, Oldbirdman, and thanks for the suggested modification. The example I provided was simply to show what can be done with a bespoke function acting on the original string. I was waiting for some confirmation from iheartvba of the format of the number sequence, and whether there are multiple positions within the string at which such a sequence may exist (in which case, like the POS function, we need to be able to generalise the function to return the start position of the second, third ... nth occurrences).

-Stewart

[ADezii]

I am trying to extract numbers from a string in the access query builder, all I need is the position of the numbers so the instr function will do. However I need a code like InStr([tblImportedIFor m].[description],"[0-9]") where ] "[0-9]" can be any number from 0 - 9. I can't figure out what to use as a wild card for numbers.


Please help

Thank You

1. The following Function will find the 1st occurrence of a Numeric Value in a String, or return 0 if there is none:
   

   Public Function fFindFirstNumeric(strString As String) As Integer
   Dim intCharPosition As Integer
   
   If Len(strString) = 0 Then Exit Function
   
   fFindFirstNumeric = 0   'Initialize to 0
   
   For intCharPosition = 1 To Len(strString)
     If IsNumeric(Mid$(strString, intCharPosition, 1)) Then
       fFindFirstNumeric = intCharPosition
         Exit Function
     End If
   Next
   End Function

2. To actually utilize this Function:
   

   Dim strTestString As String
   Dim intPositionOfNumeric As Integer
   
   strTestString = "Andromedia Strain 47 Variation"
   
   intPositionOfNumeric = fFindFirstNumeric(strTestString)
   
   If intPositionOfNumeric = 0 Then
     Debug.Print "No Numeric Value found in " & strTestString
   Else
     Debug.Print "First Numeric Value in " & strTestString & " found at Position " & intPositionOfNumeric
   End If

3. OUTPUT:
   

   First Numeric Value in Andromedia Strain 47 Variation found at Position 19

[OldBirdman]

We are looking for a function here that will be used in "access query builder". I presume then that one field of a query will be the value returned from the function. i.e. Field Row in Query Builder will be "Position: fFindFirstNumer ic([tblImportedIFor m].[description])". There can be no error messages or other warnings to the user. The function must return a valid value every time. My only intent was to point in that direction.

As a personal preference, I would remove line 4 of the code in ADezii's fFindFirstNumer ic. This relies on a default value for functions, and I don't like defaults. Always afraid of null or empty values with different versions.

[iheartvba]

Summaried Reply to All Messages:

Firstly I would like to point out that I have used the function provided by :Stewart Ross Inverness (Thanks Stewart, that's brilliant) See Post #3, I have used this to extract numbers, decimals, commas and colons from a string.

Secondly I would like to thank DonRayner for pointing out the limitations of the Instr() function. Post #2

Thirdly OldBirdman (Post 4) and ADezii (Post 6) have kindly suggested a changes in the Post#3 code , to better address my inarticulatly stated question. I have put this in my memory bank but have not tried it as post #3 already addressed the issue.

Also in reply to Oldbirdmans last post (Oldbirdman would already know this, but just for everyone else), When I am confronted with the issue of passing something in VBA to query builder I just make a form, pass the VBA value to a text box in the form then pass the value to the query builder via the text box.


Thanks Everyone

What a great feature!!