\nRefer to the following table to see what all the major topics going to be covered in this article.<\/p>\n
Index : Topics covered under this Article<\/h2>\n1. What is Regex<\/a> <\/h4>\n2. How to use RegEx in Excel VBA<\/a><\/h4>\n3. VBA to validate a string against a RegEx pattern<\/a><\/h4>\n4. VBA to Search based on a RegEx pattern<\/a><\/h4>\n5. VBA to replace strings with specific string which matches with given RegEx pattern<\/a><\/h4>\n6. Usage of RegEx in Excel VBA<\/a> <\/h4>\n7. 13 most commonly used RegEx Patterns<\/a> <\/h4>\n8. Regex Tester – Free Excel Tool Download<\/a><\/h4>\n<\/a><\/p>\nWhat is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\nThese are very useful in many ways…few of them are here…<\/strong><\/p>\n1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\n<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n
To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\n<\/a><\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
It provides basically three main methods:
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n<\/a><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n
\n\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n2# RegEx Replace Method in VBA<\/h2>\n
\u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n
\n\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n3# RegEx Test Method in VBA<\/h2>\n
– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n
\n\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\nUsage of RegEx in Excel VBA<\/h1>\n
Though as you must have already got an idea that this can be used in many ways in Excel VBA.
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\n
Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n
\n\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n
In this example, I have put this code in TextBox Change Event<\/strong>.
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
This is an example code. You can always change the RegEx pattern etc. according to your need.
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n
\n\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n13 most commonly used RegEx Patterns:<\/h1>\n
I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n
Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.
\n![\"Regex-Tester-Excel-VBA-Tool\"](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/Regex-Tester-Excel-VBA-Tool.gif\")
<\/p>\n
1. Email Validation RegEx<\/h4>\n^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\nMedium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\nSimple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n<\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n\n- Strip HTML : How to Remove HTML tags from a String in VBA<\/a><\/li>\n
- HP Quality Center – Excel Macro to Import Test Cases from QC<\/a><\/li>\n
- 5 rarely used features in Excel VBA<\/a><\/li>\n
- HP Quality Center – Create Folder Structure in Test Lab<\/a><\/li>\n
- Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]<\/a><\/li>\n<\/ol>\n
[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
Regular Expression and its usage in Excel VBA<\/title>\n<meta name=\"description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Regular Expression and its usage in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-05T22:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T19:15:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"695\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Regular Expression and its usage in Excel VBA\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"wordCount\":1300,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"articleSection\":[\"Excel Tips\",\"VBA Programming\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"name\":\"Regular Expression and its usage in Excel VBA\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"description\":\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"width\":887,\"height\":695,\"caption\":\"RegEx in Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Tips\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-tips\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Regular Expression and its usage in Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Regular Expression and its usage in Excel VBA","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Regular Expression and its usage in Excel VBA","og_description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","og_url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2018-09-05T22:18:49+00:00","article_modified_time":"2022-08-17T19:15:40+00:00","og_image":[{"width":887,"height":695,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Regular Expression and its usage in Excel VBA","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"wordCount":1300,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","articleSection":["Excel Tips","VBA Programming"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","name":"Regular Expression and its usage in Excel VBA","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","width":887,"height":695,"caption":"RegEx in Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Tips","item":"https:\/\/vmlogger.com\/excel\/excel-tips\/"},{"@type":"ListItem","position":3,"name":"Regular Expression and its usage in Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=14775"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242567"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=14775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=14775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=14775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
2. How to use RegEx in Excel VBA<\/a><\/h4>\n3. VBA to validate a string against a RegEx pattern<\/a><\/h4>\n4. VBA to Search based on a RegEx pattern<\/a><\/h4>\n5. VBA to replace strings with specific string which matches with given RegEx pattern<\/a><\/h4>\n6. Usage of RegEx in Excel VBA<\/a> <\/h4>\n7. 13 most commonly used RegEx Patterns<\/a> <\/h4>\n8. Regex Tester – Free Excel Tool Download<\/a><\/h4>\n<\/a><\/p>\nWhat is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\nThese are very useful in many ways…few of them are here…<\/strong><\/p>\n1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\n<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n
To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\n<\/a><\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
It provides basically three main methods:
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n<\/a><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n
\n\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n2# RegEx Replace Method in VBA<\/h2>\n
\u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n
\n\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n3# RegEx Test Method in VBA<\/h2>\n
– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n
\n\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\nUsage of RegEx in Excel VBA<\/h1>\n
Though as you must have already got an idea that this can be used in many ways in Excel VBA.
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\n
Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n
\n\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n
In this example, I have put this code in TextBox Change Event<\/strong>.
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
This is an example code. You can always change the RegEx pattern etc. according to your need.
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n
\n\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n13 most commonly used RegEx Patterns:<\/h1>\n
I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n
Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.
\n<\/p>\n
1. Email Validation RegEx<\/h4>\n^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\nMedium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\nSimple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n<\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n\n- Strip HTML : How to Remove HTML tags from a String in VBA<\/a><\/li>\n
- HP Quality Center – Excel Macro to Import Test Cases from QC<\/a><\/li>\n
- 5 rarely used features in Excel VBA<\/a><\/li>\n
- HP Quality Center – Create Folder Structure in Test Lab<\/a><\/li>\n
- Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]<\/a><\/li>\n<\/ol>\n
[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
Regular Expression and its usage in Excel VBA<\/title>\n<meta name=\"description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Regular Expression and its usage in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-05T22:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T19:15:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"695\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Regular Expression and its usage in Excel VBA\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"wordCount\":1300,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"articleSection\":[\"Excel Tips\",\"VBA Programming\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"name\":\"Regular Expression and its usage in Excel VBA\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"description\":\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"width\":887,\"height\":695,\"caption\":\"RegEx in Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Tips\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-tips\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Regular Expression and its usage in Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Regular Expression and its usage in Excel VBA","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Regular Expression and its usage in Excel VBA","og_description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","og_url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2018-09-05T22:18:49+00:00","article_modified_time":"2022-08-17T19:15:40+00:00","og_image":[{"width":887,"height":695,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Regular Expression and its usage in Excel VBA","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"wordCount":1300,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","articleSection":["Excel Tips","VBA Programming"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","name":"Regular Expression and its usage in Excel VBA","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","width":887,"height":695,"caption":"RegEx in Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Tips","item":"https:\/\/vmlogger.com\/excel\/excel-tips\/"},{"@type":"ListItem","position":3,"name":"Regular Expression and its usage in Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=14775"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242567"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=14775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=14775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=14775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
4. VBA to Search based on a RegEx pattern<\/a><\/h4>\n5. VBA to replace strings with specific string which matches with given RegEx pattern<\/a><\/h4>\n6. Usage of RegEx in Excel VBA<\/a> <\/h4>\n7. 13 most commonly used RegEx Patterns<\/a> <\/h4>\n8. Regex Tester – Free Excel Tool Download<\/a><\/h4>\n<\/a><\/p>\nWhat is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\nThese are very useful in many ways…few of them are here…<\/strong><\/p>\n1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\n<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n
To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\n<\/a><\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
It provides basically three main methods:
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n<\/a><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n
\n\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n2# RegEx Replace Method in VBA<\/h2>\n
\u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n
\n\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n3# RegEx Test Method in VBA<\/h2>\n
– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n
\n\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\nUsage of RegEx in Excel VBA<\/h1>\n
Though as you must have already got an idea that this can be used in many ways in Excel VBA.
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\n
Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n
\n\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n
In this example, I have put this code in TextBox Change Event<\/strong>.
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
This is an example code. You can always change the RegEx pattern etc. according to your need.
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n
\n\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n13 most commonly used RegEx Patterns:<\/h1>\n
I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n
Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.
\n<\/p>\n
1. Email Validation RegEx<\/h4>\n^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\nMedium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\nSimple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n<\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n\n- Strip HTML : How to Remove HTML tags from a String in VBA<\/a><\/li>\n
- HP Quality Center – Excel Macro to Import Test Cases from QC<\/a><\/li>\n
- 5 rarely used features in Excel VBA<\/a><\/li>\n
- HP Quality Center – Create Folder Structure in Test Lab<\/a><\/li>\n
- Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]<\/a><\/li>\n<\/ol>\n
[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
Regular Expression and its usage in Excel VBA<\/title>\n<meta name=\"description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Regular Expression and its usage in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-05T22:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T19:15:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"695\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Regular Expression and its usage in Excel VBA\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"wordCount\":1300,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"articleSection\":[\"Excel Tips\",\"VBA Programming\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"name\":\"Regular Expression and its usage in Excel VBA\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"description\":\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"width\":887,\"height\":695,\"caption\":\"RegEx in Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Tips\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-tips\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Regular Expression and its usage in Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Regular Expression and its usage in Excel VBA","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Regular Expression and its usage in Excel VBA","og_description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","og_url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2018-09-05T22:18:49+00:00","article_modified_time":"2022-08-17T19:15:40+00:00","og_image":[{"width":887,"height":695,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Regular Expression and its usage in Excel VBA","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"wordCount":1300,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","articleSection":["Excel Tips","VBA Programming"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","name":"Regular Expression and its usage in Excel VBA","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","width":887,"height":695,"caption":"RegEx in Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Tips","item":"https:\/\/vmlogger.com\/excel\/excel-tips\/"},{"@type":"ListItem","position":3,"name":"Regular Expression and its usage in Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=14775"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242567"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=14775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=14775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=14775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
6. Usage of RegEx in Excel VBA<\/a> <\/h4>\n7. 13 most commonly used RegEx Patterns<\/a> <\/h4>\n8. Regex Tester – Free Excel Tool Download<\/a><\/h4>\n<\/a><\/p>\nWhat is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\nThese are very useful in many ways…few of them are here…<\/strong><\/p>\n1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\n<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n
To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\n<\/a><\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
It provides basically three main methods:
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n<\/a><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n
\n\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n2# RegEx Replace Method in VBA<\/h2>\n
\u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n
\n\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n3# RegEx Test Method in VBA<\/h2>\n
– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n
\n\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\nUsage of RegEx in Excel VBA<\/h1>\n
Though as you must have already got an idea that this can be used in many ways in Excel VBA.
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\n
Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n
\n\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n
In this example, I have put this code in TextBox Change Event<\/strong>.
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
This is an example code. You can always change the RegEx pattern etc. according to your need.
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n
\n\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n13 most commonly used RegEx Patterns:<\/h1>\n
I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n
Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.
\n<\/p>\n
1. Email Validation RegEx<\/h4>\n^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\nMedium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\nSimple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n<\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n\n- Strip HTML : How to Remove HTML tags from a String in VBA<\/a><\/li>\n
- HP Quality Center – Excel Macro to Import Test Cases from QC<\/a><\/li>\n
- 5 rarely used features in Excel VBA<\/a><\/li>\n
- HP Quality Center – Create Folder Structure in Test Lab<\/a><\/li>\n
- Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]<\/a><\/li>\n<\/ol>\n
[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
Regular Expression and its usage in Excel VBA<\/title>\n<meta name=\"description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Regular Expression and its usage in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-05T22:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T19:15:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"695\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Regular Expression and its usage in Excel VBA\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"wordCount\":1300,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"articleSection\":[\"Excel Tips\",\"VBA Programming\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"name\":\"Regular Expression and its usage in Excel VBA\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"description\":\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"width\":887,\"height\":695,\"caption\":\"RegEx in Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Tips\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-tips\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Regular Expression and its usage in Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Regular Expression and its usage in Excel VBA","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Regular Expression and its usage in Excel VBA","og_description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","og_url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2018-09-05T22:18:49+00:00","article_modified_time":"2022-08-17T19:15:40+00:00","og_image":[{"width":887,"height":695,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Regular Expression and its usage in Excel VBA","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"wordCount":1300,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","articleSection":["Excel Tips","VBA Programming"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","name":"Regular Expression and its usage in Excel VBA","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","width":887,"height":695,"caption":"RegEx in Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Tips","item":"https:\/\/vmlogger.com\/excel\/excel-tips\/"},{"@type":"ListItem","position":3,"name":"Regular Expression and its usage in Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=14775"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242567"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=14775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=14775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=14775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
8. Regex Tester – Free Excel Tool Download<\/a><\/h4>\n<\/a><\/p>\nWhat is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\nThese are very useful in many ways…few of them are here…<\/strong><\/p>\n1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search.
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\n<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n
To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\n<\/a><\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
It provides basically three main methods:
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n<\/a><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
– In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n
\n\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n2# RegEx Replace Method in VBA<\/h2>\n
\u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n
\n\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n3# RegEx Test Method in VBA<\/h2>\n
– This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n
\n\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\nUsage of RegEx in Excel VBA<\/h1>\n
Though as you must have already got an idea that this can be used in many ways in Excel VBA.
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\n
Usage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n
\n\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n
In this example, I have put this code in TextBox Change Event<\/strong>.
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
This is an example code. You can always change the RegEx pattern etc. according to your need.
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n
\n\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n<\/a><\/p>\n13 most commonly used RegEx Patterns:<\/h1>\n
I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n
Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file.
\n<\/p>\n
1. Email Validation RegEx<\/h4>\n^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\nMedium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\nSimple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n<\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n\n- Strip HTML : How to Remove HTML tags from a String in VBA<\/a><\/li>\n
- HP Quality Center – Excel Macro to Import Test Cases from QC<\/a><\/li>\n
- 5 rarely used features in Excel VBA<\/a><\/li>\n
- HP Quality Center – Create Folder Structure in Test Lab<\/a><\/li>\n
- Methods to save a Macro FREE copy of a workbook [FREE DOWNLOAD]<\/a><\/li>\n<\/ol>\n
[\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
Regular Expression and its usage in Excel VBA<\/title>\n<meta name=\"description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Regular Expression and its usage in Excel VBA\" \/>\n<meta property=\"og:description\" content=\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2018-09-05T22:18:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-17T19:15:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\" \/>\n\t<meta property=\"og:image:width\" content=\"887\" \/>\n\t<meta property=\"og:image:height\" content=\"695\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Regular Expression and its usage in Excel VBA\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"},\"wordCount\":1300,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"articleSection\":[\"Excel Tips\",\"VBA Programming\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\",\"name\":\"Regular Expression and its usage in Excel VBA\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"datePublished\":\"2018-09-05T22:18:49+00:00\",\"dateModified\":\"2022-08-17T19:15:40+00:00\",\"description\":\"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png\",\"width\":887,\"height\":695,\"caption\":\"RegEx in Excel VBA\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Tips\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-tips\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Regular Expression and its usage in Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Regular Expression and its usage in Excel VBA","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Regular Expression and its usage in Excel VBA","og_description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","og_url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2018-09-05T22:18:49+00:00","article_modified_time":"2022-08-17T19:15:40+00:00","og_image":[{"width":887,"height":695,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Regular Expression and its usage in Excel VBA","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"},"wordCount":1300,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","articleSection":["Excel Tips","VBA Programming"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/","name":"Regular Expression and its usage in Excel VBA","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","datePublished":"2018-09-05T22:18:49+00:00","dateModified":"2022-08-17T19:15:40+00:00","description":"What is Regex ? How to use RegEx in Excel VBA . VBA to validate a string against a RegEx pattern. VBA to Search based on a RegEx pattern. VBA to replace strings with specific string which matches with given RegEx pattern. 3 Methods of RegEx. 13 most commonly used RegEx Patterns. Regex Tester - Free Excel Tool Download.","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2018\/09\/RegEx-in-Excel-VBA.png","width":887,"height":695,"caption":"RegEx in Excel VBA"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2018\/09\/regex-tester-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Tips","item":"https:\/\/vmlogger.com\/excel\/excel-tips\/"},{"@type":"ListItem","position":3,"name":"Regular Expression and its usage in Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=14775"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/14775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/242567"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=14775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=14775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=14775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
What is RegEx – Regular Expression ?<\/h1>\n
\nRegEx is basically short form of Reg<\/strong>ular Ex<\/strong>pression. It is also called as RegExp<\/strong>. Regular Expression is a pattern string which is used as a search pattern. In other words – regular expressions can be imagined as a sequence of wildcard characters.<\/p>\n These are very useful in many ways…few of them are here…<\/strong><\/p>\n 1. Wild Search: <\/strong> You can search a specific types of string which follows a specific pattern rather than providing the exact word to search. <\/a><\/p>\n VBScript has a built-in support for RegEx – Regualr Expression. It is very simple to use. <\/p>\n To use this built-in feature, you can use early binding you need to add reference in your VBA project<\/a> – VBScript.RegExp <\/a><\/p>\n It provides basically three main methods: <\/a><\/p>\n – In this method, a match from a string is extracted based on defined mathcing pattern [RegEx Pattern]. This method returns an Object which holds all the matches as an Array format. To read all the matches found, you need to loop through the object returned as shown in the below code.<\/p>\n <\/a><\/p>\n \u2013 Using this method a character or string is searched based on a defined pattern, and once found it is replaced with a new character or string defined. It returns a new string with all the matched string replaced with provided string.<\/p>\n <\/a><\/p>\n – This is used to find whether a Regex pattern is matched in a given string. It returns True or False – based on match found or not found.<\/p>\n <\/a><\/p>\n Though as you must have already got an idea that this can be used in many ways in Excel VBA. Following function can extract and list all the emailds and telephone numbers written in a string. In place of string you can ofcourse pass the value from a cell and you can extract all the email ids and phone numbers from a cell and put them in another cell using loop. It is up to you how you want to use the extracted data.<\/p>\n For example – in Excel VBA form Text Box [txtEmailID – Name of the TextBox.] You can put the following code in any of the event where you want to trigger this validation.<\/p>\n In this example, I have put this code in TextBox Change Event<\/strong>. This is an example code. You can always change the RegEx pattern etc. according to your need. <\/a><\/p>\n I have listed some most frequently used RegEx. All the following examples are also listed in the Excel Sheet which you can download at the end of the article.<\/p>\n Here in this Excel Sheet, when you select a type then automatically a regular expression pattern and one example data will appear to validate – as shown in below gif file. Strong Password:<\/strong> at least one small letter, one capital letter, one symbol and one number and it should be 8 chars long<\/p>\n Medium password:<\/strong> One capital, one small letter and one numeric field and total 6 chars long<\/p>\n Simple password: [alphanumeric]<\/strong><\/h4>\n At least one alphabet and one numeric field and total 5 chars long<\/p>\n <\/a>[\/et_pb_text][et_pb_cta title=”Download this FREE Tool – RegEx validator” button_url=”\/excel\/wp-content\/downloads\/RegexValidator-v0.01.xlsm” button_text=”Download your FREE Workbook” _builder_version=”4.17.6″ _module_preset=”a50a16dd-d05f-4ea2-acab-1468d2e4010e” global_colors_info=”{}”]In the following Excel Regex validation tool, you can type your own Regular expression pattern and enter the data which you want to validate against the pattern. You will see the result immediately whether your input data is as per the pattern you provided. [\/et_pb_cta][et_pb_blurb title=”You may like reading them:” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” sticky_enabled=”0″]<\/p>\n [\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":" Dear Reders, In this article, I am going to teach you What is Regular Expression (RegExp or RegEx)? and how RegEx is used in Excel VBA programming. You will also learn the usage of Regular Expressions in Excel VBA with some example VBA code. You can also find some simple regular expression patterns which are […]<\/p>\n","protected":false},"author":45,"featured_media":242567,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1676,5204],"tags":[],"class_list":["post-14775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-tips","category-vba-programming"],"yoast_head":"\n
\n2. Validation:<\/strong> You can validate any input or output – if it has a specific pattern
\n3. Extraction: <\/strong> Extract specific kind of words or phrases which follows specific pattern.
\n….
\nTo know more about Regular Expressions, you can also read this Wiki page –<\/em> https:\/\/en.wikipedia.org\/wiki\/Regular_expression<\/a><\/p>\nHow to use RegEx in Excel VBA<\/h1>\n
\nor else you can create Object of type VBScript.RegExp before you use it.<\/p>\nMethods of VBScript.RegEx using in VBA<\/h1>\n
\n 1. <\/strong>.Execute<\/strong>
\n 2. <\/strong>.Replace<\/strong>
\n 3. <\/strong>.Test<\/strong><\/p>\n1# RegEx Execute Method in VBA<\/h2>\n
\n
\nFunction executeMethodRegEx(regPattern As String, regString As String)\n'***************************************************************\n' Example of Execute Method of RegEx\n' Function to retrieve all the matches\n' found in a given string which matches\n' the regex Pattern string\n' Note: Returns an object holding all the matches\n' found like an array.\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' AllMatches object stores all the matches\n' returned by execute method of RegEx\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\n2# RegEx Replace Method in VBA<\/h2>\n
\n
\nFunction rePlaceMethodRegEx(regPattern As String, origString As String)\n'***************************************************************\n' Example of Replace Method of RegEx\n' Function to replace all the matches\n' found in a given string which matches\n' the regex Pattern - with a given string\n' Note: It return a new string where all the matches\n' are replace with the provided string\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\n\nWith rgx\n .Pattern = regPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' Replace method returns a new string\n' with all the matched string replaced\n' with your provided string\nDebug.Print rgx.Replace(origString, \"***abc@email.com***\")\nEnd Function\n<\/code>\n<\/pre>\n3# RegEx Test Method in VBA<\/h2>\n
\n
\nFunction testMethodRegEx(regPattern As String, regString As String) As Boolean\n'***************************************************************\n' Example of Test Method of RegEx\n' Function to check if Provided String\n' matches with the regex pattern provided\n' Note: It return as boolean - True or False\n'***************************************************************\n Dim rgx As Object\n Set rgx = CreateObject(\"VBScript.RegExp\")\n With rgx\n .Global = True\n .MultiLine = True\n .IgnoreCase = False\n .Pattern = regPattern\n End With\n ' Function returns a boolean - true\/false\n ' based on match or no match\n testMethodRegEx = rgx.test(regString)\nEnd Function\n<\/code>\n<\/pre>\nUsage of RegEx in Excel VBA<\/h1>\n
\nHere are some quite obvious usage of RegEx in Excel VBA:<\/p>\nUsage 1# Extract and List all the Email IDs and Phone Numbers from a Cell Text<\/h2>\n
\n
\nFunction listEmailIDsAndPhoneNumbers(regString As String)\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern & \"|\" & emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\nEnd With\n\n' All emailIDs adn telephone numbers would be\n' listed in this Object\nSet allMatches = rgx.Execute(regString)\n\n'Loop to read all the matches found\nFor Each Item In allMatches\n Debug.Print Item.Value\nNext\nEnd Function\n<\/code>\n<\/pre>\nUsage 2# Restrict user to type a valid format of email ID in your Excel Form<\/h2>\n
\nAs soon as you type anything in the textbox, it will start validating the text typed in the TextBox. As soon as entry is correct as per the RegEx defined, textbox back color will change to green, till then it will remain Red.<\/p>\n\n
\nPrivate Sub txtEmailID_Change()\n If Not (testMethodRegEx(\"^([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\", txtEmailID.Text)) Then\n txtEmailID.BackColor = RGB(255, 0, 0)\n Else\n txtEmailID.BackColor = RGB(0, 255, 0)\n End If\nEnd Sub\n<\/code>\n<\/pre>\nUsage 3# Replace all the email IDs and Phone Numbers with a dummy one in excel Sheet<\/h2>\n
\nIn following example, function takes a string as input and there are two patterns defined – 1. Telephone Number 2. Email ID. Based on these two regEx pattern, function will find the matched text and replace them with corresponding dummy values, you provide. Like Telephone number = +11-1111111111 or email ID as xyz@email.com etc. <\/p>\n\n
\n\nFunction maskEmailIDsAndPhoneNumbers(origString As String) As String\n'***************************************************************\n' Combined two patterns with pipe sign (|) = OR\n' Pattern1 = To validate Telephone number with Country code\n' Pattern2 = Email ID\n' This function returns the string if any one\n' of the pattern matches\n'***************************************************************\nDim rgx As Object\nSet rgx = CreateObject(\"VBScript.RegExp\")\nDim allMatches As Object\nDim emailRegPattern As String\nDim phoneNumberRegPattern As String\nemailRegPattern = \"([a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.])\"\nphoneNumberRegPattern = \"([+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9})\"\n\nWith rgx\n .Pattern = phoneNumberRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"+11-111111111\")\nEnd With\n\nWith rgx\n .Pattern = emailRegPattern\n .Global = True\n .IgnoreCase = True\n .MultiLine = True\n origString = .Replace(origString, \"xyz@email.com\")\nEnd With\nSet rgx = Nothing\n' return the masked string\nmaskEmailIDsAndPhoneNumbers = origString\n\nEnd Function\n<\/code>\n<\/pre>\n13 most commonly used RegEx Patterns:<\/h1>\n
\n<\/p>\n1. Email Validation RegEx<\/h4>\n
^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$|(([a-zA-Z\\-0-9]+\\.)+[a-zA-Z]{2,}))$<\/code><\/pre>\n2. Password validation:<\/h4>\n
^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[!@#\\$%\\^&\\*])(?=.{8,})<\/code><\/pre>\n^((?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.{6,}))<\/code><\/pre>\n^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n4. RegEx Pattern for Alphanumeric Only<\/h4>\n
^((?=.*[a-zA-Z])(?=.*[0-9])(?=.{5,}))<\/code><\/pre>\n5. RegEx Pattern for Website URL<\/h4>\n
(https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9][a-zA-Z0-9-]+[a-zA-Z0-9]\\.[^\\s]{2,}|https?:\\\/\\\/(?:www\\.|(?!www))[a-zA-Z0-9]\\.[^\\s]{2,}|www\\.[a-zA-Z0-9]\\.[^\\s]{2,})<\/code><\/pre>\n6. RegEx Pattern for Positive numbers<\/h4>\n
^([+]?\\d+([.]\\d+))<\/code><\/pre>\n7. RegEx Pattern for Negative numbers<\/h4>\n
^(\\-\\d+([.]?\\d+))<\/code><\/pre>\n8. RegEx Pattern for 10 digit telephone number [not starting with Zero]<\/h4>\n
[1-9]{1}[0-9]{9}$<\/code><\/pre>\n9. RegEx Pattern for Phone with a country code format<\/h4>\n
[+]{1}[0-9]{2}[-]{1}[1-9]{1}[0-9]{9}$<\/code><\/pre>\n10. RegEx Pattern for Year between 1900 and 2018<\/h4>\n
(19[0-9]{2}|200[0-9]{1}|201[0-8]{1})$<\/code><\/pre>\n11. RegEx Pattern for Date format : DD\/mm\/yyyy<\/h4>\n
^((0[1-9]|[1-9]|[12][0-9]|3[0-1])\\\/(0[1-9]|[1-9]|1[0-2])\\\/([12]\\d{3}))<\/code><\/pre>\n12. RegEx Pattern for NL Postal Code<\/h4>\n
\\d{4}[ ]*([aA-zZ]{2})$<\/code><\/pre>\n13. RegEx Pattern for US Postal Code format<\/h4>\n
^\\d{5}(?:[-\\s]\\d{4})?$<\/code><\/pre>\n\n