Introduction to Regular Expressions in Excel

Author: David H. Ringstrom

In this course, author and Excel expert David H. Ringstrom, CPA, will explore the latest features available in the Microsoft 365 Excel Beta Channel for both Windows and Mac users, including powerful new functions like XLOOKUP and XMATCH. Attendees will learn how to leverage Regular Expressions (REGEX) with functions such as REGEXTEST, REGEXEXTRACT, and REGEXREPLACE to streamline data manipulation and validation. David will also demonstrate practical applications of XLOOKUP for approximate matches and its integration with Excel Tables, alongside traditional functions like VLOOKUP and SUMIF, highlighting their wildcard nuances. Additionally, discover how Power Query can enhance your data analysis with fuzzy matching techniques, making your workflows more efficient and effective. Join us for an insightful course that will elevate your Excel skills to new heights!

David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the course and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Publication Date: March 2025

Designed For
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Breaking down regular expression patterns
• Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365
• Understanding the nuances of approximate matches with XLOOKUP, which can be greater than or less than the look-up value
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula
• Discovering how to use wildcards and multiple criteria within lookup formulas
• Performing fuzzy matches with Power Query
• Searching for text with the REGEXTEST function
• Defining regular expressions
• Replacing text based upon pattern matching with REGEXREPLACE
• Using VLOOKUP to perform approximate matches
• Comparing the new XMATCH function in Excel 2021 and Microsoft 365 to the MATCH function available in all versions of Excel
• Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells

Learning Objectives

• Identify what to specify in the match_mode argument to cause XLOOKUP to return an approximate match based upon the next smaller item
• Identify the appropriate use of an operator within the SUMIF function
• Identify the menu in Excel where the Table feature resides
• Identify a core component of regular expressions
• Identify a valid criterion for performing a partial match in text data with worksheet functions

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None