RegExp Object: An Alternative to InStr() Function ?
Did you ever encounter a situation where you needed to search a sub-string inside a string? Of course InStr() is a great function which helps users solve this problem. But what happens when the sub-string you want to search contains a regular expression? InStr() doesn’t help when the user wants to search a regular expression. In such situations, RegExp object comes to your rescue. Let’s see how..
|
sString = “Welcome User, You have 5 New Mails” sSubString = “Welcome .*, You have .* New Mails”‘Check if the string pattern matches – using RegExp object Set objRE = New RegExp objRE.Global = True objRE.Pattern = sSubString msgbox objRE.Execute(sString).Count ‘ — msgbox returns the value 1 as the RegExp object returns 1 match‘Check if the string pattern matches – using InStr msgbox InStr(1, sString, sSubString, 1) ‘ — msgbox returns the value 0 as it is not able to find the match using InStr |
Let’s explore all the features of RegExp object
|
Set regEx = New RegExp
regEx.Pattern = “test”
regEx.Global = True ‘ — or False
msgbox regEx.Execute(“test testing tested”).Count
|
In this example, if the value of Global property is set as True, the msgbox count would come as 3. If the Global property value is set as False, the msgbox count comes as 1.
|
Set regEx = New RegExp
regEx.Pattern = “test”
regEx.Global = True regEx.IgnoreCase = True ‘ — or False msgbox regEx.Execute(“Test testing TeSted”).Count
|
Here, if the IgnoreCase property is taken as True, then the msgbox count will come as 3. For the value False, msgbox returns the count as 1.
c) Pattern Property: This property specifies the actual pattern that needs to be matched with the string. The pattern string can contain regular expressions also. [Read more about regular expression patterns here]
|
Set regEx = New RegExp
regEx.Pattern = “test”
regEx.Global = True
Set objResult = regEx.Execute(“test testing tested”)
msgbox objResult.Count |
b) Replace Method:
Replace method replaces the text found in a regular expression search. The method takes 2 parameters –
object.Replace(string1, string2)
string1 - the string in which the text needs to be replaced string2 - the replacement string
|
|
str1 = “long longer longest”
str2 = “short”
Set regEx = New RegExp
regEx.Pattern = “long”
regEx.Global = True sReplace = regEx.Replace(str1, str2)
msgbox sReplace
|
In this example, the text ‘long’ is replaced by ‘short’ and the msgbox returns short shorter shortest
|
Set regEx = New RegExp
regEx.Pattern = “test”
regEx.Global = True msgbox regEx.Test(“test testing tested”)
|
In this example, the msgbox returns the Boolean value True as the pattern match is found.
If you enjoyed this article, you can join our blog to get free email updates directly in your inbox.
