remove alpha & special characters from field

[Guest]

Hi -

I have an alpha numeric field with entries of varying length and would like
to just keep the numbers

C/O NICK W MANFIELD9653 W BLACKJACK RIDGE

would become 9653

Any help would be appreciated! Thanks

 

[John Spencer]

Here is a bit of code I wrote a while back. Copy it and put it into a
module. Then you can call it when you want.


'----------------- Code starts ------------------------
Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function
'---------- Code ends ----------------