Extract Numbers from Cell if Number is 3 or 4 digits in access programming

[teco1]

Dear All

Need your support. I want to extract Number from a cell if that number is 3 or 4 digits. I want to do it in access using sql so that i can call this function in my query.

For example

DUM_NS_3808_38081_F5_A
BLK_DUM_NS_0512_05122_F6_A

The Output should be
3808
512

Many Thanks

 

[Joe4]

Will each entry have only one 3 or 4 digit number, or could they have more than one?
I would probably create a User Defined Function in VBA to do this. You can use these in Queries.
If this a valid option for you?

 

[Joe4]

Here is a UDF that will return all 3 or 4 digit numbers, no matter how many exist (if multiple, they are separated by commas)

Function GetNumber(myString As String) As String

    Dim myArray() As String
    Dim i As Long
    Dim mySubWord As String
    Dim myReturn As String

'   Create array of values, when delimited by underscore
    myArray = Split(myString, "_")
    
'   Loop through all delimited values and build string of matching values
    For i = LBound(myArray) To UBound(myArray)
        mySubWord = myArray(i)
        If (Len(mySubWord) = 3 Or Len(mySubWord) = 4) And IsNumeric(mySubWord) Then
            myReturn = myReturn & mySubWord & ","
        End If
    Next i
    
'   Remove last comma from string
    If Len(myReturn) > 1 Then GetNumber = Left(myReturn, Len(myReturn) - 1)
        
End Function

So, in your query, you would just use it like any other Function in a Calculated field, i.e.
MyNumbers: GetNumber([FieldName])

 

[teco1]

Thanks Joe4 for your response. It works great.
Is it possible to return First 3 or 4 digits which are separated by an "_" . Appreciate if this could.


I am using below mention UDF in excel and works fine and return First 3 or 4 digits.

Function Get_Store(oCell As Range) As Long
Dim aryWords, strWord
aryWords = Split(oCell.Value, "_")
For Each strWord In aryWords
If IsNumeric(strWord) Then
If Len(strWord) = 3 Or Len(strWord) = 4 Then
Get_Store = CLng(strWord)
Exit For
End If
End If
Next
End Function

 

[Joe4]

Is it possible to return First 3 or 4 digits which are separated by an "_" . Appreciate if this could.

Try using the code I posted instead. It does that precise thing.

 

[teco1]

your code return all 3 or 4 digit numbers, no matter how many exist (if multiple, they are separated by commas)
But i need to return First 3 or 4 digits that are seperated by an underscore. Thanks

 

[Joe4]

This variation will stop once it finds one and return that one only:

Function GetNumber(myString As String) As String

    Dim myArray() As String
    Dim i As Long
    Dim mySubWord As String

'   Create array of values, when delimited by underscore
    myArray = Split(myString, "_")
    
'   Loop through all delimited values and build string of matching values
    For i = LBound(myArray) To UBound(myArray)
        mySubWord = myArray(i)
        If (Len(mySubWord) = 3 Or Len(mySubWord) = 4) And IsNumeric(mySubWord) Then
            GetNumber = mySubWord
            Exit For
        End If
    Next i
    
End Function

 

[teco1]

Thanks much Joe4. It worked for me. Thanks much

 

[Joe4]

You are welcome!
I think that was the first time I used the Split function and Arrays of undetermined size in Access VBA. It was kind of fun!