Removing the first letter and sometimes last letter of a 9 digit number

[sharzi]

So, I have some data that has to be 9 digit integer, however, some of them have A or W or E at first and then 9 integers and some of them have other letters, sometimes two letter at the end of 9 digits.

I tried to use accel and this command:

Left([Field],9) in the update section, but it only gives me the firs 9 and sometimes cut the last digits and comes with the first letters.

What criteria can I use that can only target digits not letter?

 

[arnelgp]

my idea is to create a function using RegExp:

Public Function Search9Digit(sString As String)
    
    Dim oRE, oMatches, oMatch
    Dim var As Variant
    
    Const strPattern As String = "[0-9]{9}"
    
    Set oRE = CreateObject("VBScript.RegExp")
    With oRE
        .Global = True
        .IgnoreCase = True
        .Pattern = strPattern
        Set oMatches = .Execute(sString)
    End With
    
    For Each oMatch In oMatches
        Search9Digit = Trim(CStr(oMatch))
    Next
    Set oMatch = Nothing
    Set oMatches = Nothing
    Set oRE = Nothing
End Function

if you are going to call it in query:


Digit: CLng(Search9Digit([Field]))

 

[sharzi]

my idea is to create a function using RegExp:

Public Function Search9Digit(sString As String)
    
    Dim oRE, oMatches, oMatch
    Dim var As Variant
    
    Const strPattern As String = "[0-9]{9}"
    
    Set oRE = CreateObject("VBScript.RegExp")
    With oRE
        .Global = True
        .IgnoreCase = True
        .Pattern = strPattern
        Set oMatches = .Execute(sString)
    End With
    
    For Each oMatch In oMatches
        Search9Digit = Trim(CStr(oMatch))
    Next
    Set oMatch = Nothing
    Set oMatches = Nothing
    Set oRE = Nothing
End Function

if you are going to call it in query:


Digit: CLng(Search9Digit([Field]))

Thanks.

Can you tell me how can I use codes?

 

[arnelgp]

Goto VBE (Alt-F11). On menu, Isert Module. Paste the code in the module.
If you are going ti use it in Query:

Select Field1, Search9Digit(Field1) As Digits From yourTable;