Extract numbers from text 1

[ftook]

Could anyone advise the best way to extract a number portion of a specific string i.e.

1234/rejnf23 - to be 123423
00277g- 99 - to be 0027799 still need the leading 00's
pp3pp3pp3 - to be 333

etc

 

[JerryKlmns]

Ascii values for numbers, range from 48 to 57. If you loop the characters in the string, crate a new one when these are found.

Would you try?

 

[PHV]

You may use this function:

Public Function getNum(myField)
Dim S As String, l As Long, x As String
If Trim(myField & "") = "" Then
  getNum = myField
  Exit Function
End If
For l = 1 To Len(myField)
  x = Mid(myField, l, 1)
  If x >= "0" And x <= "9" Then
    S = S & x
  End If
Next
getNum = S
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ed2020]

Id do something like this:

   For x = 1 to len(strToCheck)
      If IsNumeric(mid(strToCheck, x)) Then
         strReturnValue=mid(strToCheck, x))
      End If
   Next x

Ed Metcalfe.

Please do not feed the trolls.....

 

[ftook]

Thanks PHV - works great

how can i now integrate that into a dcount function to again strip out each comparison field for [Internal No] field within the table

i.e. compare myfield within getNum([Internal No])

If DCount("[Unitno]", "Unit Works Order Header", "[Internal No] = '" & myfield & "'") > 0 Then