# Find a number in a specified string of characters

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
###### Who is Participating?

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

Commented:

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 Commented:
Is there a simpler way to do this?
0

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

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

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

Regards,

Rob.
0

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

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

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

Commented:
I agree Matthewpatrick should get the credit.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.