How to select only numbers from a text field

Question

How can I pull only the number from a field and put that value into its own field?

For example, if a field1 contains a value of "Name(1234U)".

I need an SQL or VBA way to scan that field and pull the number out. So field2 will equal "1234".

Any ideas?

Answer 1

Create a VBA function, call it as a field in your query and pass it the original field (like select GetNumerals(field1) from table...). The VBA function will loop through each letter in the field and return only the numeric values. It could look something like this:

Public Function GetNumerals(str As String) As String

Dim i As Integer

    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
            GetNumerals = GetNumerals & Mid(str, i, 1)
        End If
    Next i

End Function

Answer 2

It is possible that this or a variation may suit:

 SELECT t.Field1, Mid([Field1],InStr([field1],"(")+1,4) AS Stripped
 FROM TheTable As t

For example:

 UPDATE TheTable AS t SET [field2] = Mid([Field1],InStr([field1],"(")+1,4);

EDIT re comment

If the field ends u), that is, alpha bracket, you can say:

 UPDATE TheTable AS t SET [field2] =
 Mid([Field1],InStr([field1],"(")+1,Len(Mid([Field1],InStr([field1],"(")))-3)

Answer 3

The following VBA function might do the trick:

Option Compare Database
Option Explicit

Public Function RegexReplaceAll( _
        OriginalText As Variant, _
        Pattern As String, _
        ReplaceWith As String) As Variant
    Dim rtn As Variant
    Dim objRegExp As Object  ' RegExp

    rtn = Null
    If Not IsNull(OriginalText) Then
        Set objRegExp = CreateObject("VBScript.RegExp")  ' New RegExp
        objRegExp.Pattern = Pattern
        objRegExp.Global = True
        rtn = objRegExp.Replace(OriginalText, ReplaceWith)
        Set objRegExp = Nothing
    End If
    RegexReplaceAll = rtn
End Function

Example using the regular expression pattern

[^0-9]+

which matches one or more non-digit characters

RegexReplaceAll("Name(1234U)","[^0-9]+","")

returns

1234

edit:

To use this in a query run from within the Access application itself, try something like

SELECT Field1, RegexReplaceAll(Field1,"[^0-9]+","") AS JustNumbers
FROM Table1;