Regular-Expressions (RegExp) is something I bumped into when my string manipulation requirements jumped to an advanced level. Before I started using this powerhouse, all my string manipulation functions involved maneuvering through a dozen for-loops; and tackling hundreds of Left-Right-Mid-InStr functions. RegExp is a pattern matching system that allows you to perform advanced string manipulations very easily. It may take a while to get used to it, but once you get the hang of it, the possibilities are endless.
To tap into this super power, a Reference to “Microsoft VBScript Regular Expressions 5.5” has to be added to the VBA Project. Click here to see how to add the reference to your project. Unfortunately, the version implemented in Visual Basics, using VBScript Scripting Library, does not include Regular Expressions in its entirety. Some of the new features have not been implemented yet. Click here to check out which features are available.
RegExp in VBA is fashioned as an Object. Pattern, Global and IgnoreCase are the properties you might need to use. Replace is the one method that I use very often. Please remember that the primary purpose of this post is the ease you into getting the hang of the Replace method.
Steps to using the Replace Method:
- Declare and Initialize the RegExp Object
- Set the IgnoreCase property to False, if you do not want the pattern to be case sensitive. It is set to True by default
- Set the Global property to True, to replace all instances of matched pattern. It is set to False by default i.e. only the first instance is replaced.
- This is the most important step. Set the Pattern property. I advise going through the links I have posted below to get the hang of using this.
- Finally, we use the Replace method and assign it to a string variable.
It is a good idea to wrap this into a nice neat function, so you do not have to declare new objects or change the properties of existing ones explicitly. This function can be used within VBA and can also be used in the Excel Formula Bar; we will see examples of both. I can’t imagine doing string manipulations in Excel without RegExp. I intend to post a lot of other user defined functions in the future that use the following function:
'==================================================================================== 'Wrapper function to impliment the Replace method of the RegExp object 'Author : Ejaz Ahmed - firstname.lastname@example.org 'Date: 08 December 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Function RegExpReplace(ByVal WhichString As String, _ ByVal Pattern As String, _ ByVal ReplaceWith As String, _ Optional ByVal IsGlobal As Boolean = True, _ Optional ByVal IsCaseSensitive As Boolean = True) As String 'Declaring the object Dim objRegExp As Object 'Initializing an Instance Set objRegExp = CreateObject("vbscript.regexp") 'Setting the Properties objRegExp.Global = IsGlobal objRegExp.Pattern = Pattern objRegExp.IgnoreCase = Not IsCaseSensitive 'Execute the Replace Method RegExpReplace = objRegExp.Replace(WhichString, ReplaceWith) End Function
This function can be wrapped inside other functions to perform string manipulations easily. Below are some very simple examples of how the above function could be used in your VBA functions.
Function to extract only numbers in a string:
'==================================================================================== 'Function to extract only the numbers in a string using RegExp 'Author : Ejaz Ahmed - email@example.com 'Date: 08 December 2013 'Website : https://strugglingtoexcel.wordpress.com/ '==================================================================================== Function OnlyNumbers(ByVal WhichString As String) As Variant OnlyNumbers = CDbl(RegExpReplace(WhichString, _ "[^0-9]", vbNullString, True)) End Function
The screenshot below contains few examples of RegExpReplace() being used in the Excel interface. I would suggest experimenting with the Pattern and Replace-With strings to test your ideas; and finally wrap them into user defined function of your own.
Please feel free to share your ideas here. Finally, I found the following links very useful and I hope they help you too:
- VB/VBA regular expression
- Microsoft Beefs Up VBScript with Regular Expressions
- Regular Expression Syntax (Scripting)
- Regular Expression Object Properties and Methods (VBScript)