Logo
Back

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops?

By default, Microsoft Excel does not include built-in functions for regular expressions. However, you can still leverage regex by using:

  1. VBA (Visual Basic for Applications) with the built-in RegExp object, and
  2. Custom functions (UDFs) or macros, which can be used in cells or in loops through your worksheets.

Below is a step-by-step guide to using regex in Excel:

1. Enable the Developer Tab (if not visible already)

  1. Go to File → Options → Customize Ribbon.
  2. Check Developer in the right pane.
  3. Click OK.

This allows you to access the Visual Basic Editor and create macros or custom functions.

2. Enable “Microsoft VBScript Regular Expressions” Reference

Within the Visual Basic Editor, you can (optionally) add a reference for easier auto-completion:

  1. Go to Tools → References.
  2. Check "Microsoft VBScript Regular Expressions 5.5" (or similar).
  3. Click OK.

You can use the CreateObject("VBScript.RegExp") method even without setting the reference, but having the reference is handy for IntelliSense.

3. Using Regex in a VBA Macro (Loops)

Here’s an example that iterates through rows in Column A and applies a regex pattern to detect or replace text.

Sub RegexLoopExample()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long
    Dim regEx As New RegExp    ' Requires reference OR use CreateObject("VBScript.RegExp")
    Dim cellText As String
    
    ' Specify the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last used row in Column A
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Configure the RegExp object
    With regEx
        .Pattern = "[A-Za-z]+"  ' Example pattern: letters only
        .IgnoreCase = True
        .Global = False
    End With
    
    ' Loop through each row
    For i = 1 To LastRow
        cellText = ws.Range("A" & i).Value
        
        If regEx.Test(cellText) Then
            ' If there's a match, do something (e.g. highlight)
            ws.Range("A" & i).Interior.Color = vbYellow
        End If
    Next i
End Sub

Explanation:

  • Dim regEx As New RegExp: Creates an instance of the RegExp object.
  • .Pattern = "[A-Za-z]+": The pattern for matching.
  • .Test(cellText): Checks if the pattern is found in the string. Returns True/False.

You could also do .Replace(cellText, "some replacement") if you want to replace matches.

4. Creating a Custom Function (UDF) for In-Cell Regex

4.1 Simple “Test” UDF

You can create a function that returns TRUE or FALSE if a regex pattern is found in a string. For example:

Function RegexTest(str As String, pattern As String) As Boolean
    Dim regEx As New RegExp
    
    regEx.Pattern = pattern
    regEx.IgnoreCase = False
    regEx.Global = True
    
    RegexTest = regEx.Test(str)
End Function

Usage in a cell:

=RegexTest(A2, "[0-9]+")
  • A2 is the text you want to evaluate.
  • "[0-9]+" is the pattern (this can also be a cell reference like B2 if you store the pattern in the sheet).

The cell formula will show TRUE if A2 contains one or more digits, FALSE otherwise.

4.2 “Replace” UDF

If you want a function to replace matches within a string:

Function RegexReplace(str As String, pattern As String, replacement As String) As String
    Dim regEx As New RegExp
    
    regEx.Pattern = pattern
    regEx.IgnoreCase = False
    regEx.Global = True
    
    RegexReplace = regEx.Replace(str, replacement)
End Function

Usage in a cell:

=RegexReplace(A2, "[^0-9]+", "")

This would remove all non-digit characters from A2.

5. Use Cases

  1. Validation: E.g., check if a cell matches a pattern for phone numbers, emails, etc.
  2. Cleaning Data: Remove unwanted characters or reformat strings with regex.
  3. Extraction: Pull out specific parts of a string (though you might need a slightly more complex UDF that returns capturing group results).

6. Alternatives & Tips

  1. Power Query: If you have a modern version of Excel, consider using Power Query (Get & Transform). Power Query’s M language supports certain text transformations, though it’s not as straightforward as full regex.
  2. Office Scripts or Scripts in Excel Online: If you’re using Excel on the web, you can use Office Scripts (TypeScript-based) or connect Excel with Power Automate for advanced text manipulation.
  3. External Tools: For big data cleansing, you might export your data to a CSV and use dedicated scripts in Python, R, or shell commands with sed/awk.

7. Summary

  1. Enable Developer: Access the Visual Basic Editor.
  2. Use RegExp in VBA: Reference Microsoft VBScript Regular Expressions 5.5 or use CreateObject("VBScript.RegExp").
  3. Macros (Loops): Create a procedure that iterates cells, applies .Test(), .Replace(), etc.
  4. UDFs: Write custom functions for in-cell usage, e.g., =RegexTest(A2, "[pattern]").
  5. Best approach: If you need robust transformations or large-scale operations, consider Power Query or external scripts.

Bonus: Level Up Your Data Skills

If you work heavily with data in Excel but also want to handle more advanced coding or interviews, here are some recommended courses from DesignGurus.io:

  1. Grokking the Coding Interview: Patterns for Coding Questions
    Strengthen your problem-solving skills with pattern-based solutions, crucial for interviews and day-to-day engineering.

  2. Grokking Data Structures & Algorithms for Coding Interviews
    Dive deeper into DS & A fundamentals—helps with large data manipulation strategies beyond Excel macros.

For real-time feedback, explore Coding Mock Interviews or System Design Mock Interviews with ex-FAANG engineers. Also, find free tutorials on the DesignGurus.io YouTube channel.

Conclusion: You can’t directly do regex in an Excel formula by default, but you can:

  1. Use VBA to create macros or custom functions (UDFs) leveraging the RegExp object.
  2. Call those UDFs in cells (e.g., =RegexTest(...)) or in loops in a macro.
CONTRIBUTOR
TechGrind