Extracting Numbers form a text field

[DwightD]

I am trying to extract the numeric values from a text field. For example, if the field is "345 6th Street Apt 84", would like to get a numeric result field of '345684'. Any ideas?

 

[revans]

Something like this

Public Function ExtractNumbers(str As String) As String
'loop through str and return the numbers
Dim temp As String
Dim i As Long
If Len(Nz(str, "")) > 0 Then
  For i = 1 To Len(str)
    If IsNumeric(Mid$(str, i, 1)) Then
      temp = temp & Mid$(str, i, 1)
    End If
  Next i
End If
ExtractNumbers = temp
End Function

I left the function to return a string an not a long because the parameter might be a zero-length string and it won't convert to a number. I'd call it like this

temp=ExtractNumbers(""345 6th Street Apt 84"")
If len(temp)>0 then
' do stuff here
end if

hth,

Rich