Experts Exchange connects you with the people and services you need so you can get back to work.
Private Function Min(ByVal valueA As Long, ByVal valueB As Long) As Long
If valueA < valueB Then
Min = valueA
Min = valueB
Public Function ExtractFirstNumber(ByVal strSourceString As String) As Variant
Dim strNumber As String
Dim l As Long
Dim c As String * 1
Dim i As Long
Dim j As Long
Dim last As Long
l = Len(strSourceString)
For i = 1 To l
c = Mid(strSourceString, i, 1)
If IsNumeric(c) Or ((c = "." Or c = "+" Or c = "-") And IsNumeric(Mid(strSourceString, Min(i + 1, l), 1))) Then
strNumber = c
For j = i + 1 To Len(strSourceString)
c = Mid(strSourceString, j, 1)
If Not IsNumeric(strNumber & c) Then Exit For
strNumber = strNumber & c
ExtractFirstNumber = CDec(strNumber)
Open in new window
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 = <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()
Static 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 = ""
Pos = CLng(Pos)
' Create instance of RegExp object
If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
.Pattern = PatternStr
.Global = True
.IgnoreCase = Not MatchCase
' 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)
RegExpFind = Answer
' User wanted the Nth match (or last match, if Pos = 0). Get the Nth value, if possible
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 = ""
' If there are no matches, return empty string
RegExpFind = ""
' Release object variables
Set TheMatches = Nothing
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.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Premium members get this course for $159.20.
Premium members get this course for $151.20.
Premium members get this course for $95.20.
Premium members get this course for $143.20.
Premium members get this course for $174.99.
Premium members get this course for $108.00.
Premium members get this course for $25.00.