Your technology certification is waiting. Enroll in Cloud Class ®
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()
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 = ""
Pos = CLng(Pos)
' Create instance of RegExp object
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 RegX = Nothing
Set TheMatches = Nothing
Open in new window
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Dim SearchString, SearchChar, MyAtPos, MyStartPos, MyEndPos, FoundEmailString
SearchString = "This Message was undeliverable due to the following reason: Your message was not delivered because the destination computer refused to accept it (the error message is reproduced below). This type of error is usually due to a mis-configured account or mail delivery system on the destination computer; however, it could be caused by your message since some mail systems refuse messages with invalid header information, or if they are too large. Your message was rejected by mx1c9.bellcanadahosting.com for the following reason: 5.7.1 mA8NUEDm007560 This message does not comply with required standards. The following recipients did not receive this message: <firstname.lastname@example.org>" ' String to search in.
SearchChar = "@" ' Search for "@".
' A textual comparison starting at position 1. Returns 1326.
MyAtPos = Instr(1, SearchString, SearchChar, 1)
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.