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!

Microsoft Access

Last Comment

aikimark

8/22/2022 - Mon

crystal (strive4peace) - Microsoft MVP, Access

12/2/2015

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

12/3/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

aikimark

12/3/2015

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23