434,661 Members | 1,917 Online
Need help? Post your question and get tips & solutions from a community of 434,661 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

7 Replies

 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 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 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 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 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

 Expert 5K+ P: 8,634 @iheartvba 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 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   To actually utilize this Function: Expand|Select|Wrap|Line Numbers 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   OUTPUT: Expand|Select|Wrap|Line Numbers 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