Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find a number in a specified string of characters

Posted on 2008-10-28
10
Medium Priority
?
1,996 Views
Last Modified: 2013-11-25
How do I find a number in a specified string of characters?  I am looking for an "M" immediately followed by 9 numbers (any numbers) within a string.

strFid = Mid(sBuffer, intCounter, 10)
   If strFid = "M" 'plus 9 numbers' Then
0
Comment
Question by:RichFrot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22824461
Add this UDF to your project:


Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True)

    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841

    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos =   : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
   
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
   
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
   
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
   
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
   
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
       
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
       
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
       
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
       
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
   
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
   
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
   
End Function




Then use it like this:


If RegExpFind(strFid, "M\d{9}", 1, False) <> "" Then
    'found
Else
    'not found
End If
0
 

Author Comment

by:RichFrot
ID: 22824724
Is there a simpler way to do this?
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 22825950
Hi, the above code from Patrick is actually a very robust function, although it is VBA code.  I have converted it to VBScript for you.

Regards,

Rob.
strData = "New_M123456789_Member"
If RegExpFind(strData, "M\d{9}", 1, False) <> "" Then
    MsgBox "Data valid"
Else
    MsgBox "Data not valid"
End If
 
Function RegExpFind(LookIn, PatternStr, Pos, MatchCase)
 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841
 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Dim RegX, TheMatches, Answer, Counter
    
    If IsNull(MatchCase) Then MatchCase = True
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsNull(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsNull(Pos) Then
            ReDim Answer(TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            If Pos = 0 Then                          ' Last match
                 RegExpFind = TheMatches(TheMatches.Count - 1)
            ElseIf Pos = 1 Then
                 RegExpFind = TheMatches(Pos - 1)
            Else                       ' Invalid item number
                 RegExpFind = ""
            End If
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
    
End Function

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22826883
Thanks, Rob.  The function works in both VBA and VB 6, and I appreciate your making it VBScript-friendly :)

(Couldn't tell from the question exactly which environment this is supposed to run in...)

BTW, if you liked that one, feel free to use that and it's sister, RegExpReplace, which is also VBA and VB6
compliant:

Function RegExpReplace(LookIn As String, PatternStr As String, Optional ReplaceWith As String = "", _
    Optional ReplaceAll As Boolean = True, Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
   
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
   
    ' If you use this function from Excel, you may substitute range references for all the arguments
   
    Dim RegX As Object
   
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
   
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
   
    Set RegX = Nothing
   
End Function
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 22827571
Awesome, thanks!  Saves me having to re-write Regular Expression matching!

Regards,

Rob.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22827894
Yeah, I wrote bth of them because I wanted to have quick plug and play for RegExp :)
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 22827940
Great job. Thanks!

P.S. Here's the VBScript version of the RegExpReplace function....
strData = "New_M123456789_Member"
strNewString = RegExpReplace(strData, "123", "XYZ", True, False)
MsgBox strNewString
 
Function RegExpReplace(LookIn, PatternStr, ReplaceWith, ReplaceAll, MatchCase)
 
    ' This function uses Regular Expressions to parse a string, and replace parts of the string
    ' matching the specified pattern with another string.  The optional argument ReplaceAll controls
    ' whether all instances of the matched string are replaced (True) or just the first instance (False)
    
    ' By default, RegExp is case-sensitive in pattern-matching.  To keep this, omit MatchCase or
    ' set it to True
    
    ' If you use this function from Excel, you may substitute range references for all the arguments
    
    If IsNull(ReplaceWith) Then ReplaceWith = ""
    If IsNull(ReplaceAll) Then ReplaceAll = True
    If IsNull(MatchCase) Then MatchCase = True
    
    Dim RegX
    
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = ReplaceAll
        .IgnoreCase = Not MatchCase
    End With
    
    RegExpReplace = RegX.Replace(LookIn, ReplaceWith)
    
    Set RegX = Nothing
    
End Function

Open in new window

0
 
LVL 29

Accepted Solution

by:
nffvrxqgrcfqvvc earned 2000 total points
ID: 22829610
You can use IsNumeric function to check if the character is a number.
Option Explicit
 
Public Function isNineNumbers(ByVal arg As String) As Long
    
    Dim szChar      As String
    Dim j           As Long
    isNineNumbers = 0
    For j = 1 To Len(arg)
        szChar = Mid$(arg, j, 1)
        If IsNumeric(szChar) Then
            isNineNumbers = (isNineNumbers + 1)
        End If
    Next j
        
End Function

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22831670
RichFrot,

With respect, please explain why you chose egl1044's response.  (egl1044 did nothing wrong here, and I
mean no disprespect to him/her.)

Your question was very clear about wanting to see the letter 'M' followed by nine numerals.  My suggestion
does precisely that, and, strictly speaking, egl1044's does not.  Not to mention that my suggestion came
in 20 hours earlier.

If there were anything about my suggestion that you did not understand or that was unclear, I would have
been pleased to help more had you but mentioned it.

Regards,

Patrick
0
 

Expert Comment

by:raddad555
ID: 23508444
I agree Matthewpatrick should get the credit.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question