Extract only first available numeric from a string using access vba

[Harshitha B M]

For an example if column value is "ABC 123 981" need to extract only 123... like so if its "456_wert" need to extract only 456 using access VBA code. Can somebody please help on this.

 

[rlv01]

One example.

Sub Example()
    Dim RX As Object
    Dim S As String, ValStr As String
    Dim Msg As String
    
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "[^0-9 _]"
    
    S = "ABC_123 981"
    ValStr = Split(Trim(RX.Replace(Replace(S, "_", " "), "")), " ")(0)
    Msg = "String: " & S & vbCr & "Value: " & ValStr
    
    Msg = Msg & vbCr & vbCr
    
    MsgBox Msg
End Sub

(Note: I am assuming late binding is possible under the Access object model as it is for Excel and Word)

 

[Micron]

Yes, it is possible. Likely the code needs to return a value, which subs cannot do, so it might need to be a function. If that function is written to accept a parameter, then that might be better than hard coding the string. However, I get that the code is only for providing an excellent solution (I have tried to learn RegEx but it just doesn't seem to sink in) so well done! As a function:

Function Example(strIn As String) As Long 'assumes number type of Long is wanted vs anything else
    Dim RX As Object

    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "[^0-9 _]"
    Example = Split(Trim(RX.Replace(Replace(strIn, "_", " "), "")), " ")(0)
    
End Function

How it might be called: myVariable = example("ABC 123 981")
or if a return message is needed:
MsgBox Example("ABC 123 981")

 

[rlv01]

Yes, it is possible. Likely the code needs to return a value, which subs cannot do, so it might need to be a function. If that function is written to accept a parameter, then that might be better than hard coding the string. However, I get that the code is only for providing an excellent solution (I have tried to learn RegEx but it just doesn't seem to sink in) so well done! As a function:

Function Example(strIn As String) As Long 'assumes number type of Long is wanted vs anything else
    Dim RX As Object

    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "[^0-9 _]"
    Example = Split(Trim(RX.Replace(Replace(strIn, "_", " "), "")), " ")(0)
  
End Function

How it might be called: myVariable = example("ABC 123 981")
or if a return message is needed:
MsgBox Example("ABC 123 981")

Yes, it should probably be a function, but the OP did not explain anything about how it was going to be used, so I wanted a simple example to focus on the string extraction. Hence the hard-coding.