By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Extracting a Number from a string

100+
P: 171
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([tblImportedIForm].[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
Feb 5 '09 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Public Function fExtractNumeric(strInput) As String
  2.     ' Returns the numeric characters within a string in
  3.     ' sequence in which they are found within the string
  4.     Dim strResult As String, strCh As String
  5.     Dim intI As Integer
  6.     If Not IsNull(strInput) Then
  7.         For intI = 1 To Len(strInput)
  8.             strCh = Mid(strInput, intI, 1)
  9.             Select Case strCh
  10.                 Case "0" To "9"
  11.                     strResult = strResult & strCh
  12.                 Case Else
  13.             End Select
  14.         Next intI
  15.     End If
  16.     fExtractNumeric = strResult
  17. End Function
Example input: "abc def g123456hijk lmn"
Output: "123456"

-Stewart

Share this Question
Share on Google+
7 Replies


DonRayner
Expert 100+
P: 489
You can't use wildcards in the InStr() function. It will treat wildcards as just another character.
Feb 6 '09 #2

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function fExtractNumeric(strInput) As String
  2.     ' Returns the numeric characters within a string in
  3.     ' sequence in which they are found within the string
  4.     Dim strResult As String, strCh As String
  5.     Dim intI As Integer
  6.     If Not IsNull(strInput) Then
  7.         For intI = 1 To Len(strInput)
  8.             strCh = Mid(strInput, intI, 1)
  9.             Select Case strCh
  10.                 Case "0" To "9"
  11.                     strResult = strResult & strCh
  12.                 Case Else
  13.             End Select
  14.         Next intI
  15.     End If
  16.     fExtractNumeric = strResult
  17. End Function
Example input: "abc def g123456hijk lmn"
Output: "123456"

-Stewart
Feb 6 '09 #3

100+
P: 675
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:
Expand|Select|Wrap|Line Numbers
  1. fExtractNumeric = intI 
  2. Exit Function
  3.  
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
Feb 6 '09 #4

Expert Mod 2.5K+
P: 2,545
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
Feb 7 '09 #5

ADezii
Expert 5K+
P: 8,599
@iheartvba
  1. The following Function will find the 1st occurrence of a Numeric Value in a String, or return 0 if there is none:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fFindFirstNumeric(strString As String) As Integer
    2. Dim intCharPosition As Integer
    3.  
    4. If Len(strString) = 0 Then Exit Function
    5.  
    6. fFindFirstNumeric = 0   'Initialize to 0
    7.  
    8. For intCharPosition = 1 To Len(strString)
    9.   If IsNumeric(Mid$(strString, intCharPosition, 1)) Then
    10.     fFindFirstNumeric = intCharPosition
    11.       Exit Function
    12.   End If
    13. Next
    14. End Function
    15.  
  2. To actually utilize this Function:
    Expand|Select|Wrap|Line Numbers
    1. Dim strTestString As String
    2. Dim intPositionOfNumeric As Integer
    3.  
    4. strTestString = "Andromedia Strain 47 Variation"
    5.  
    6. intPositionOfNumeric = fFindFirstNumeric(strTestString)
    7.  
    8. If intPositionOfNumeric = 0 Then
    9.   Debug.Print "No Numeric Value found in " & strTestString
    10. Else
    11.   Debug.Print "First Numeric Value in " & strTestString & " found at Position " & intPositionOfNumeric
    12. End If
    13.  
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. First Numeric Value in Andromedia Strain 47 Variation found at Position 19
Feb 7 '09 #6

100+
P: 675
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: fFindFirstNumeric([tblImportedIForm].[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 fFindFirstNumeric. This relies on a default value for functions, and I don't like defaults. Always afraid of null or empty values with different versions.
Feb 7 '09 #7

100+
P: 171
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!!
Feb 9 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.