asked on

Extract numbers only from a string

I have an access table with a field called Item PN with examples below

Item_PN
B32853
528034
87394E

I want to extract only the numbers from the string so that the result is:
32853
528034
87394

thanks!

crystal (strive4peace) - Microsoft MVP, Access

here is a public function you can use (modify if the return data type is not Long Integer):

Public Function GetNumberFromString(pString As String) As Long
'140827 strive4peace
   Dim i As Integer _
      , sNumber As String
      
   sNumber = ""
  
   For i = 1 To Len(pString)
      Select Case Asc(Mid(pString, i))
         Case 48 To 57 '0-9
            sNumber = sNumber & Mid(pString, i, 1)
         Case Else
            'no more digits
            GetNumberFromString = CLng(sNumber)
            Exit Function
      End Select
   Next i

   GetNumberFromString = CLng(sNumber)

End Function

Open in new window

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

Please test this function.

Function GetNumbers(parmValue)
    Static oRE As Object

    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "[A-Z]"
    End If
    
    If IsNull(parmValue) Then
        GetNumbers = Null
    End If
    
    GetNumbers = oRE.Replace(parmValue, "")

End Function

Open in new window