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
Medium Priority
1,996 Views
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
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

LVL 93

Expert Comment

ID: 22824461

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

' 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 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)
Next

' 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
End If
0

Author Comment

ID: 22824724
Is there a simpler way to do this?
0

LVL 65

Expert Comment

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)

' 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()

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
For Counter = 0 To UBound(Answer)
Next

' 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
``````
0

LVL 93

Expert Comment

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

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

Regards,

Rob.
0

LVL 93

Expert Comment

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

LVL 65

Expert Comment

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

LVL 29

Accepted Solution

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

LVL 93

Expert Comment

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

Regards,

Patrick
0

Expert Comment

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

## Featured Post

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
Course of the Month9 days, 12 hours left to enroll