The Takeaway: Excel just got a whole lot more powerful with the introduction of REGEX functions! These nifty tools let you validate data, extract specific info, and replace patterns with ease. Say goodbye to data entry headaches and hello to clean, consistent spreadsheets, with a little help from AI.
Skill Level: Intermediate
Watch the Tutorial
Download the Excel File
You can download the workbook used in this guide here:
Meet Excel’s New REGEX Functions
Excel recently rolled out three new REGEX functions designed to simplify finding and extracting complex patterns in your data. This guide will walk you through how to use these functions to prevent those frustrating data entry errors. We’ll also explore how to use AI tools like Copilot or ChatGPT to make writing those formulas a breeze!
Diving into REGEX Functions
REGEX Test
The REGEXTEST function is your go-to for checking if a specific pattern exists in a cell. Need to know if your account codes contain any numbers? Here’s how:
To check if account codes contain numbers:
- Type
=REGEXTEST(B6,"[0-9]")in the cell where you want the result. The pattern[0-9]looks for any digit between 0 and 9.
- Drag the formula down to apply it to the rest of the column. You’ll see
TRUEfor cells containing numbers andFALSEotherwise.
- For complex patterns, like detecting phone numbers in scraped data, you’ll need a more robust REGEX pattern.
Fortunately, we can lean on AI to help write those complex patterns. More on that soon!
REGEX Extract
The REGEXTEXTRACT function does exactly what it says: extracts data matching a pattern from a text string.
- Type
=REGEXEXTRACT(B6, "your_phone_number_pattern")to extract phone numbers.
- To return all matches, use
=REGEXEXTRACT(B6, "your_phone_number_pattern", 1). This will spill the results into adjacent cells.
- To avoid those pesky spill errors, wrap it in TRANSPOSE:
TRANSPOSE(REGEXEXTRACT(B6, "your_phone_number_pattern", 1)).
REGEX Replace
The REGEXREPLACE function lets you find a pattern and swap it out with something else.
To standardize phone number formats, use =REGEXREPLACE(B6, "your_phone_number_pattern", "replacement_format").
You can also use it to format dates consistently!
Enhancing Data Validation with REGEX
REGEX is incredibly useful for data validation. Ensure data integrity by incorporating REGEX into your data validation rules.
- Copy your
REGEX.TESTformula, like=REGEXTEST(C7, "your_sku_pattern"). - Select the cells where you want the data validation applied, go to the Data tab, click Data Validation, choose Custom, and paste the formula. Adjust cell references as needed.
Using Conditional Formatting with REGEX
Want to flag non-compliant entries? Conditional formatting with REGEX is your friend. Highlight cells that *don’t* match your specified pattern.
- Copy your
REGEXTESTformula. - Select the cells, go to the Home tab, click Conditional Formatting, choose New Rule, use a formula, paste the formula, and format the cells to highlight mismatches.
Once you hit OK, the entries that don’t comply with your pattern will be highlighted, making them easy to spot.
Leveraging AI Tools for Complex Patterns
REGEX patterns can be tricky to create. That’s where AI shines! Use tools like ChatGPT or Copilot to assist you. Give ChatGPT your pattern requirements, like, “Please help create a REGEX expression for the following pattern…”
ChatGPT doesn’t just give you the expression, it breaks down the components, too! Copy the generated pattern into your Excel formula.
Another helpful resource is regex101.com, where you can test your patterns and get assistance understanding the language.
