How to Replace String With Regular Expression in MS Access

Regular Expression is a very powerful technique or tools that can help save much of your time in writing your code.
Like other development tools, Regular Expression is bundled into MS Access as well. There are a few steps that you can apply Regular Expression. Below is 4 step that you can leverage regular expression in MS Access:

1. Go to and click on Tools menu bar then again click on References sub menu.
2. Tick to choose Microsoft VBScript Regular Expression x.x the last version (here in my example version 5.5).
3. Click OK
4. Add the vbscript code below.

Voila! you have replace regular expression ready to use.

Add the following code into your VBA code:

Public Function regexp_replace( _
    ByVal SourceString As String, _
    ByVal Pattern As String, _
    ByVal ReplacePattern As String, _
Optional ByVal IgnoreCase As Boolean = True, _
    Optional ByVal MultiLine As Boolean = True, _
    Optional ByVal MatchGlobal As Boolean = True) As String
 
    With New RegExp
        .MultiLine = MultiLine
        .IgnoreCase = IgnoreCase
        .Global = MatchGlobal
        .Pattern = Pattern
        regexp_replace = .Replace(SourceString, ReplacePattern)
    End With
 
End Function

Example of Regular Expression Usage

To replace all of the occurrences of number in string use the following code

regexp_replace("1234ABa","[0-9]","")

To replace all of the occurrences of character in string use the following code

regexp_replace("1234ABa","[a-z]","")

And last be not least, this is how you use this regular expression in your query

SELECT 
   regexp_replace("1234ABa","[a-z]","") AS Expr1
FROM 
   dual;