VBA Regex Pattern Validation

The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard and are followed worldwide. Following those standards, we can extract and list all the values from the excel datasheet just within one click. Below are the few common regex validation patterns used in day to day activities.

  1. Email Validation using Regex

 In email validation, two things are constant, i.e. ‘@’ and ‘.’. With the help of these two characters we have defined the regex pattern. In the first place, we can have as many characters, it will be followed by ‘@’, then again we will have characters (unlike Gmail) followed by ‘.’ and at last ending it characters (com/in/co.in).

Regex Syntax:

Program:

VBA Regex Pattern Validation

Output:

VBA Regex Pattern Validation
VBA Regex Pattern Validation

2. Regex Validation Pattern for Alphanumeric Strings

It must have a combination of alphabets and numbers. It will mostly be used in passwords and hence, alphanumeric strings are classified as Strong passwords.

Regex Syntax:

Program:

3. RegEx Pattern for Positive numbers

It will check for the number which is greater than 0.

Regex Syntax:

Program:

VBA Regex Pattern Validation

4. RegEx Validation for Negative numbers (num < 0)

It will check for the number which is smaller than 0.

Regex Syntax:

Program:

VBA Regex Pattern Validation

Output

VBA Regex Pattern Validation

5. RegEx Pattern for 10-digit telephone number [not starting with Zero]

It will check for a 10-digit telephonic number. As the first digit can never be 0, so it will check the number from 1-9, and for the next 9 digits, it can take values from 0-9.

Regex Syntax:

Program:

VBA Regex Pattern Validation

Output

VBA Regex Pattern Validation
VBA Regex Pattern Validation

6. RegEx Pattern for Phone with a country code format

It same unlike the above syntax except in this we have added the ‘+’ character and allocated two more digits, which can be from 0-9.

Regex Syntax:

Program:

7. RegEx Pattern for Year between 1900 and 2018

This is helpful to check if the specified year lies in between the range of 1900 – 2018.

Regex Syntax:

Program:

VBA Regex Pattern Validation

Output:

VBA Regex Pattern Validation

Some Other useful Regex Patterns

  1. Password validation using Regex
  • Strong Password: It consists at least one small letter, one capital letter, one symbol, and one number, and it should be 8 chars long.

Regex Syntax:

  • Medium password: It consists one capital, one small letter, and one numeric field, and total 6 chars long.

Regex Syntax:

  • Simple password: It takes at least one alphabet, and one numeric field [alphanumeric], and the total length should be 5 characters long.

Regex Syntax:

  • RegEx Pattern for Date format : DD/mm/yyyy

Regex Syntax:

  • Regex Pattern for NL Postal Code

Regex Syntax:

  • Regex Pattern for US Postal Code format

Regex Syntax:

  • RegEx Pattern for Website URL

Pin It on Pinterest

Share This