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.
Open your Microsoft Access database or Excel spreadsheet, then go to VBA Editor pressing Alt-F11 combination.
Create a new module calling context menu on the root node of the project tree.
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.
Close VBA Editor.
Create a table and a query in Microsoft Access and a column of values in Microsoft Excel to test the function.