Learn when you want, where you want with convenient online training courses. Sign up now!
[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
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: <email@example.com>" ' String to search in.
SearchChar = "@" ' Search for "@".
' A textual comparison starting at position 1. Returns 1326.
MyAtPos = Instr(1, SearchString, SearchChar, 1)
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()
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
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.