Regular Expressions 2

Welcome! Extract Emails Validate Dates With Regex

Welcome to Access Developer 66. We will continue our work with Regular Expressions by extracting email addresses, removing duplicates with Scripting.Dictionary, cleaning imported text by handling extra spaces and tabs, and preserving original data while updating cleaned records. We will also build patterns to recognize multiple date formats, address American and British date conventions, and validate extracted dates with reusable VBA functions.

Navigation

Prev: D66
Next: Lesson 1

Keywords

Access Developer, VBA regular expressions, RegEx email extraction, remove duplicate emails, Scripting.Dictionary, clean imported text, remove extra spaces and tabs, date validation, date regex, ISO date pattern, American vs British dates, DateSerial valid

Subscribe to Regular Expressions 2
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 66, brought to you by Access Learning Zone. I'm your instructor, Richard Rost.

Today, we're continuing our work with regular expressions. We're going to put them to use solving some real-world database problems.

We'll start by extracting email addresses from large blocks of text and eliminating duplicates. Then we'll clean up messy imported data by removing extra spaces and tabs. Finally, we'll tackle one of the biggest headaches for database developers: dates. We'll learn how to recognize several different date formats, deal with American and British date conventions, and make sure that dates we find are actually valid.

And along the way, we'll build several reusable VBA functions that you can add to your own databases.

This class follows Access Developer Level 65, where we introduced regular expressions and built our basic RegEx functions. Developer 65 is especially important for today's class because we'll be reusing the functions and building on the patterns we learned there. If you haven't watched Developer 65 first, you're not going to understand anything I'm talking about today. So go watch Developer 65 first.

You should also be comfortable with VBA functions, loops, and arrays from earlier developer lessons, and also the date functions. And like I always say, don't skip levels because I assume you know all the material from the previous classes in every class. So go watch them all. It's like Pokemon, you gotta catch them all.

I will be using Microsoft 365. It is currently November of 2026, so the closest retail version would be Access 2024. But the stuff in this video should work for every version of Access going back to 2007.

If you've got any questions about today's class, scroll down to the bottom of the page that you're on and post them there. It's much easier for me and the other moderators to answer your questions if they're posted on the right page. That way, we don't have to go finding the video that you're talking about.

And if you've got questions about anything non-related to the class, post them in the Access forum. That way, everybody else can join in and help answer your question.

Alright, let's take a closer look at what is covered in today's class.

In lesson one, we'll start with a quick review of the regular expressions that we learned in Developer 65 and introduce our RegEx cheat sheet. Then we'll build a function that extracts email addresses from a block of text. We'll also introduce Scripting.Dictionary to eliminate duplicate email addresses and save our new pattern in our RegEx table.

In lesson two, we'll learn how to clean up messy imported text. We'll work with whitespace patterns to remove extra spaces and tabs without destroying line breaks. Then we'll build a reusable cleanup function and use an Access query to preview and update our imported records while preserving the original information.

And in lesson three, we'll tackle dates in all kinds of different formats. We'll build individual patterns for ISO dates, numeric dates, and dates with written month names. Then we'll combine those into one expression. We'll learn about word boundaries, handle the differences between American and British date formats, write VBA functions to validate the dates we extract, and we'll even see why DateSerial alone isn't enough to determine whether a date is valid.

By the end of this level, you'll have several useful RegEx tools you can reuse in your own applications. You'll know how to extract information from messy text, clean up imported records, and recognize and validate dates from several sources. You'll also have a handy RegEx cheat sheet covering the material from Developers 65 and 66.

As always, I recommend you watch each video once through completely and then watch it a second time following along with the examples. It'll make much more sense if you watch it first and then try doing it yourself. Then apply it to any databases that you're working on.

Sit back, relax, grab your coffee, or other beverage of choice. I know some of you complained in the last video that you're not all coffee drinkers. I didn't start drinking coffee until I was well into my 20s.

Sit back, relax, and get ready for lesson one. Thanks for watching.

Intro

Welcome to <B>Access Developer 66</B>. We will continue our work with Regular Expressions by extracting email addresses, removing duplicates with Scripting.Dictionary, cleaning imported text by handling extra spaces and tabs, and preserving original data while updating cleaned records. We will also build patterns to recognize multiple date formats, address American and British date conventions, and validate extracted dates with reusable VBA functions.

Summary

Today's video from Access Learning Zone continues our work with regular expressions in Microsoft Access VBA. I will show you how to apply the RegEx concepts from Developer Level 65 to practical database tasks that you are likely to encounter when working with imported or unstructured data.

This is Microsoft Access Developer Level 66. In this level, we will use regular expressions to extract useful information from large blocks of text, clean up inconsistent imported records, and identify dates written in a variety of formats.

We will begin by extracting email addresses from text fields that may contain paragraphs, notes, comments, or other unstructured information. I will show you how to find all of the email addresses in a block of text, collect them, and eliminate duplicates. We will use a Scripting.Dictionary to make sure that the same email address is not returned more than once. We will also save the new email pattern in our RegEx table so it can be reused later.

Next, we will work with messy imported text. Imported data often contains extra spaces, tab characters, and inconsistent formatting. We will use whitespace patterns to remove unnecessary spaces and tabs while preserving line breaks where they are needed. Then we will build a reusable VBA cleanup function that can be applied to imported records. I will also show you how to use an Access query to preview the cleaned data before updating records, allowing you to preserve the original imported information.

The final lesson focuses on dates, which are one of the most common sources of data problems in databases. Dates can be written in many different ways, including ISO format, numeric month-day-year or day-month-year formats, and dates that use written month names. We will build separate regular expression patterns for each type and then combine them into a single expression.

Along the way, we will cover word boundaries so that we can avoid matching dates that are embedded inside other text or numbers. We will also discuss the difference between American and British date conventions. A date such as 4/5/2026 can mean April 5 in one system or May 4 in another, so it is important to understand the assumptions your database is making.

We will create VBA functions to validate the dates that we find. Simply passing values to DateSerial is not always enough, because DateSerial can adjust invalid dates instead of rejecting them. For example, a date with too many days in a month may roll forward into the next month. I will show you how to properly determine whether an extracted date is truly valid.

This class builds directly on Microsoft Access Developer Level 65, where I introduced regular expressions and created the basic RegEx functions that we will use again here. Developer 65 is required before taking this class. If you have not completed it, go back and watch that lesson first. You should also be comfortable with VBA functions, loops, arrays, and Access date functions from the earlier developer classes.

I am using Microsoft 365, and the current date is November 2026. The closest perpetual retail version is Access 2024. However, the techniques covered in this class should work with Microsoft Access versions going back to Access 2007.

By the end of this level, you will have several reusable RegEx tools for your own Access databases. You will know how to extract email addresses from unstructured text, eliminate duplicate values, clean up messy imported records, recognize several common date formats, and verify that dates are valid before using them. You will also have a RegEx cheat sheet covering the material from Developer Levels 65 and 66.

I recommend watching each lesson all the way through once before trying the examples yourself. Then watch the lesson again and follow along step by step in your own database. After that, apply the techniques to your own projects so that the material becomes familiar.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Regular expressions course overview and prerequisites

Primary Topics

email address extraction from text, duplicate email removal, whitespace cleanup, tabs and extra spaces, imported text cleanup, date pattern recognition, date validation, American versus British date formats, reusable VBA RegEx functions

Secondary Topics

RegEx pattern review, RegEx cheat sheet, Scripting.Dictionary, Access queries, preserving original imported data, word boundaries, DateSerial limitations