|
||||||
Regular ExpressionsWelcome! Text Extraction Validation and CleanupWelcome to Access Developer 65. We will learn how to use regular expressions in VBA to search, validate, extract, replace, and clean text data. We will cover reusable RegEx functions, pattern libraries, case sensitivity, multiple matches, capture groups, phone number formatting, ZIP code validation, and extracting invoice numbers from notes with inconsistent formatting. NavigationKeywordsAccess Developer, VBA regular expressions, RegEx pattern matching, VBA RegExp object, RegEx Test function, RegEx Extract function, RegEx Replace function, capture groups, ZIP code validation, ZIP+4 validation, phone number cleanup, invoice number extracti
More InformationTranscriptWelcome to Microsoft Access Developer Level 65, brought to you by Access Learning Zone. I am your instructor, Richard Rost. In this level, we're going to learn about regular expressions, or reg-X for short, or reg-X, or however you want to pronounce it. If you've ever needed to search through text, validate data, extract specific information, or clean up messy imported records, regular expressions can make your life a whole lot easier. We'll start with the basics, learning how regular expressions work and how to use them in VBA. Then we'll build our own reusable functions for testing patterns, extracting matching values, and replacing text. We'll create a library of useful patterns that we can save and reuse, learn how to work with capture groups, and finish with a practical example that pulls invoice numbers out of customer notes, even when those invoice numbers aren't all formatted the same way. By the time we're finished, you'll have your own little reg-X toolkit that you can use throughout your Access databases. This class follows Access Developer Level 64. You should be comfortable with VBA functions, collections, and working with forms. As always, I recommend taking all of the previous developer classes in order, as we will be basing a lot of this off of a lot of the other stuff we've learned. So I recommend you don't skip levels. But if you're just looking to learn regular expressions and you haven't taken any of my previous classes, you should have a solid foundation and a good understanding of VBA before starting today's class. I will be using Microsoft 365. It's currently October of 2026. Regular expressions are available to users of older Access versions through the VBA Script Regular Expression engine, provided that component is available on their system. I'll explain the small code differences in the first lesson. All right, let's take a look at what's covered in today's class. In lesson one, we're going to start with the basics of regular expressions. We'll compare traditional VBA string searching with pattern matching, learn how to check whether text contains numbers, and validate five-digit ZIP codes. Then we'll move into extracting matching values, finding multiple matches using Global, and controlling case sensitivity with IgnoreCase. This lesson combines my free RegEx TechHelp video and its extended cut. If you've already watched both, you can skip ahead to lesson two. And as always, I don't count previously released TechHelp material toward the hour of new developer instruction that you're getting in this level. So consider lesson one bonus material. In lesson two, we're going to take the regular expressions code from lesson one and turn it into reusable VBA functions. We'll build our own RegEx Test and RegEx Extract functions, with options for case sensitivity and finding multiple matches, and create a little interface that lets us experiment with different patterns. We'll also learn how to return multiple matching values as a semicolon-separated string. In lesson three, we're going to build a table to store our favorite regular expression patterns so you don't have to remember them or type them every time we need them. We'll add a combo box to select those patterns, learn how to validate ZIP+4 postal codes, and build our own RegEx Replace function. Then we'll use it to clean up imported phone numbers by removing unwanted characters and leaving just the digits. In lesson four, we're going to learn about capture groups. These let us identify and remember specific pieces of a matching value, and then put those pieces back together in whatever order or format we want. We'll start by reformatting phone numbers and then move into finding and reformatting multiple phone numbers buried inside customer notes without changing the surrounding text. We'll also see why this can be much more powerful than simply using the Format function. And finally, in lesson five, we're going to put everything together with a practical business example. Suppose you're importing customer notes or emails that contain invoice numbers, but everybody formats those invoice numbers differently. We'll learn how to recognize different invoice labels, handle optional separators like dashes, colons, or number signs, and then extract just the invoice numbers themselves while ignoring unrelated numbers in the text, like a phone number. Then we'll build a reusable function that retrieves its pattern from our database and returns all of the matching invoice numbers. This is where regular expressions really start to shine. So by the end of this level, you'll understand how to use regular expressions to solve text processing problems that would otherwise require a lot of complicated string functions and special case logic. And don't worry about memorizing every regular expression symbol. I've been using this stuff for years, and I still look up patterns all the time. What's important is understanding how they work, how to test them, and how to put them to use in your applications. All right, as always, I recommend watching each lesson once through completely and then watching it a second time while following along with the examples. And if this is your first time using regular expressions, I strongly recommend you do that, especially with all of these lessons. They'll make more sense once you've seen them and you understand where we're going with them instead of just blindly typing along with me. So watch each lesson once, see where I'm going with it, and then watch it again and follow along. All right, sit back and relax, grab your coffee, and it's time to start lesson one. IntroWelcome to <B>Access Developer 65</B>. We will learn how to use regular expressions in VBA to search, validate, extract, replace, and clean text data. We will cover reusable RegEx functions, pattern libraries, case sensitivity, multiple matches, capture groups, phone number formatting, ZIP code validation, and extracting invoice numbers from notes with inconsistent formatting. SummaryWelcome to Microsoft Access Developer Level 65 from Access Learning Zone. I am Richard Rost, and in this course we are going to work with regular expressions, commonly called RegEx. Regular expressions are a powerful way to search text, validate entered values, pull specific pieces of information from larger strings, and clean up inconsistent imported data. They can save you from having to write long chains of InStr, Mid, Replace, and other traditional VBA string functions when you need to handle more complicated text patterns. We will begin with the fundamentals of how regular expressions work in Access VBA. From there, we will create reusable tools that you can use throughout your databases. By the end of the course, you will have a practical RegEx toolkit for testing patterns, extracting information, replacing text, and managing commonly used expressions. This course follows Access Developer Level 64. You should already be comfortable working with VBA functions, collections, and forms. I recommend taking the Developer series in order because the lessons build on concepts introduced in earlier classes. If you are joining specifically to learn regular expressions, you should still have a solid understanding of VBA before beginning. I am using Microsoft 365 as of October 2026. Regular expressions can also be used in older versions of Access through the VBA Script Regular Expression engine, assuming that component is available on your computer. In the first lesson, I will explain the small differences you may encounter depending on how you set up the reference and instantiate the regular expression object. In lesson one, we will cover the basics of regular expressions. I will compare ordinary VBA string searching with pattern matching, show you how to determine whether text contains numbers, and validate five-digit ZIP codes. We will then move into extracting matching values, finding multiple matches with the Global property, and controlling case sensitivity with IgnoreCase. Lesson one combines material from my free RegEx TechHelp video and its Extended Cut. If you have already watched both of those videos, you can move ahead to lesson two. As always, previously released TechHelp material does not count toward the new Developer Level instruction. In lesson two, we will take the basic RegEx code and turn it into reusable VBA functions. We will create functions for testing whether text matches a pattern and extracting matching values from text. These functions will include options for case sensitivity and for returning either one match or all matches. We will also build an interface that allows us to experiment with patterns, test different values, and return multiple matches as a semicolon-separated string. In lesson three, we will create a table for storing useful regular expression patterns. This will allow you to save your favorite patterns in the database instead of having to remember or retype them every time you need them. We will add a combo box for selecting stored patterns, validate ZIP+4 postal codes, and create a reusable RegEx Replace function. We will then use that replacement function to clean imported phone numbers by stripping out unwanted characters and retaining only the digits. This is a common example of how regular expressions can simplify the cleanup of imported data. In lesson four, we will work with capture groups. Capture groups allow you to identify individual portions of a matching value, remember those portions, and then rearrange or reformat them. We will begin by reformatting phone numbers and then move on to locating and reformatting multiple phone numbers within longer customer notes, while leaving the rest of the note unchanged. This technique is much more flexible than relying only on the Format function because regular expressions can locate patterns within unstructured text and work with individual parts of each match. In lesson five, we will combine everything in a practical business example involving invoice numbers. Suppose you import customer notes or email messages that contain invoice numbers entered in several different formats. One person may type "Invoice 12345," another may use "Inv-12345," and someone else may include a number sign, colon, dash, or other separator. We will create a pattern that recognizes the different invoice labels and optional separators, extracts only the invoice numbers, and ignores unrelated numbers in the same text, such as phone numbers. Finally, we will build a reusable function that retrieves its regular expression pattern from the database and returns all matching invoice numbers. By the end of this course, you will understand how regular expressions can solve text-processing problems that would otherwise require complicated VBA string functions and lots of special-case logic. You do not need to memorize every RegEx symbol. I have used regular expressions for years, and I still look up patterns when I need them. The important thing is understanding how patterns work, how to test them, and how to apply them in your Access applications. I recommend watching each lesson all the way through once before following along. Then watch it a second time while building the examples yourself. This is especially important if regular expressions are new to you. Seeing the overall goal first will make the individual steps much easier to understand when you work through the examples. 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 ListRegular expressions course overview Course prerequisites and recommended preparation Primary Topicsregular expressions in VBA, pattern matching, reusable RegEx functions, stored pattern library, capture groups, text replacement, invoice number extraction Secondary Topicstraditional VBA string searching comparison, case sensitivity, multiple matches, ZIP code validation, ZIP+4 validation, phone number cleanup, form interface for testing patterns, collections and VBA prerequisites |
||
|
| |||
| Keywords: Access Developer, VBA regular expressions, RegEx pattern matching, VBA RegExp object, RegEx Test function, RegEx Extract function, RegEx Replace function, capture groups, ZIP code validation, ZIP+4 validation, phone number cleanup, invoice number extracti PermaLink Build Reusable RegEx Tools for Pattern Matching, Text Extraction, Validation, and Cleanup |
