Extract Number in Microsoft Access/Excel

Microsoft Access and Excel don't include any string functions to extract a number from a string. It should be created a custom function to complete this task. Regular Expressions are a good option to deal with manipulation of text data. Microsoft Access and Excel are lacking of support of regular expressions but they allow to utilize third party libraries. A library of our interest is Microsoft VBScript Regular Expressions 5.5 one.

1. Open your Microsoft Access database or Excel spreadsheet, then go to VBA Editor pressing Alt-F11 combination.

   Microsoft Access

   

   Microsoft Excel

   

2. Create a new module calling context menu on the root node of the project tree.

   Microsoft Access

   

   Microsoft Excel

   

3. Copy and paste the function below to the new created module.

   Function ExtractNumber(textValue)
   
       Dim re As Object
       Set re = CreateObject("vbscript.RegExp")
   
       re.Pattern = "[^\d]"
       re.Global = True
   
       ExtractNumber = re.Replace(textValue, "")
   
   End Function
   

   This code uses late binding to the library. This method is not preferable but it reduces number of steps to implement the solution.

4. Close VBA Editor.

5. Create a table and a query in Microsoft Access and a column of values in Microsoft Excel to test the function.

   Microsoft Access

   

   Microsoft Excel