extract 3 numbers from a string

[gerenrich]

I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01

 

[vbaInet]

Basically the first point the number could start is the 3rd?

Welcome to the forum!

 

[gerenrich]

It can start anywhere from pos 2 thru about 8

there are almost 60k records, so possibly after pos 8, but i didnt see any after that point, don't think that will matter so much, maybe.

and thank you for the welcome

 

[vbaInet]

I can't see anyone that starts from pos 2?

 

[gerenrich]

sorry, i must not have included one of those. here is one example of one.
One key i did notice thru all records, there is always an alpha character before the 3 numerical digits which are needed, just a note

A59252-1

again in this example, 592 being the desired numbers

thanks

 

[plog]

I think you need to write a custom function in a module. You would pass it your Code value and it would return the value you wanted. Here's the pseudo code for that function:

set LastLetter variable to position of last letter in Code
(http://techniclee.wordpress.com/2010/07/21/isletter-function-for-vba/)

set ReturnValue variable to 3 characters in Code from LastLetter to LastLetter + 3
(http://www.techonthenet.com/access/functions/string/mid.php)

set ReturnValue to Null if ReturnValue isn't numeric
(http://www.techonthenet.com/excel/formulas/isnumeric.php)

return the ReturnValue

 

[gerenrich]

let me take a look and see.

thanks

 

[vbaInet]

Here's some quick code not tested extensively but it should do it:

Public Function Return3Nums(ByVal PartNumber As Variant) As String
    Const I_START As Byte = 2
    Dim iMax      As Integer
    Dim iCount    As Byte
    
    If Len(PartNumber & vbNullString) = 0 Then Exit Function
    
    iMax = Len(PartNumber)
    
    For i = I_START To iMax
        If iCount <= I_START Then
            If IsNumeric(Mid(PartNumber, i, 1)) Then
                Return3Nums = Return3Nums & Mid(PartNumber, i, 1)
                If iCount = 2 Then Exit For
                iCount = iCount + 1
            Else
                Return3Nums = vbNullString
                iCount = 0
            End If
        End If
    Next
    
    If Len(Return3Nums) < 3 Then Return3Nums = vbNullString
End Function

 

[vbaInet]

set ReturnValue to Null if ReturnValue isn't numeric
(http://www.techonthenet.com/excel/formulas/isnumeric.php)

return the ReturnValue

If you want to do this just change the two vbNullString to Null and String to Variant.

 

[gerenrich]

Awesome,
Sorry had to get a bite to eat, let me check this out and see if i have any other questions....
thanks so much for all this input

I will get back with a response as soon as I can

 

[gerenrich]

Here's some quick code not tested extensively but it should do it:

Public Function Return3Nums(ByVal PartNumber As Variant) As String
    Const I_START As Byte = 2
    Dim iMax      As Integer
    Dim iCount    As Byte
    
    If Len(PartNumber & vbNullString) = 0 Then Exit Function
    
    iMax = Len(PartNumber)
    
    For i = I_START To iMax
        If iCount <= I_START Then
            If IsNumeric(Mid(PartNumber, i, 1)) Then
                Return3Nums = Return3Nums & Mid(PartNumber, i, 1)
                If iCount = 2 Then Exit For
                iCount = iCount + 1
            Else
                Return3Nums = vbNullString
                iCount = 0
            End If
        End If
    Next
    
    If Len(Return3Nums) < 3 Then Return3Nums = vbNullString
End Function

You are awesome, used the function as-is and it gives just what I'm after. Can't express in words how grateful I am to you for this help.

 

[vbaInet]

Good to hear!

I would do what was mentioned in post #9 if you are actually wanting Number otherwise if you want the numbers as Text then it's fine as-is.

 

[vbaInet]

And if you want a different approach you can look into Regular Expressions.

 

[gerenrich]

Good to hear!

I would do what was mentioned in post #9 if you are actually wanting Number otherwise if you want the numbers as Text then it's fine as-is.

Actually this is perfect as it allows me to link to another table that is provided to me that contains definitions of the codes and the codes are listed as text, so all-in-all just perfect.
again, can't thank you enough. Awesome work