Extracting numbers from a text string

[mkleino]

Hi, I was wondering if anyone knows how to extract numbers from a text string. I have a notes field that often has year information somewhere in the midst of text, and I want to extract all numbers, but no other characters, from that field. Are there any functions/expressions I can use to accomplish this? Or, if someone has an idea of code that might work, I'd appreciate a bit of help on how to use it to create or update a table, as I'm not very experienced in that area. Thanks very much!

-Mike

 

[raskew]

Try this:

Function scrubomatic(thestuff As String, KeepNum As Boolean) As String
'*******************************************
'Name: scrubomatic (Function)
'Purpose: Remove all numeric or non-numeric
' characters from a string
'Inputs: from debug window:
' (1) To remove numeric characters ? scrubomatic("abc$123 xyz456", True)
' (2) To remove non-numeric characters ? scrubomatic("abc$123 xyz456", False)
'Output:
' (1) 123456
' (2) abc$ xyz
'*******************************************

Dim strHold As String, intLen As Integer, n As Integer
strHold = RTrim(thestuff)
intLen = Len(strHold)
strHold = ""
For n = 1 To intLen
If KeepNum = False Then 'Remove numeric characters
strHold = strHold & IIf(Not IsNumeric(Mid(thestuff, n, 1)), Mid(thestuff, n, 1), "")
Else 'retain only numeric characters
strHold = strHold & IIf(IsNumeric(Mid(thestuff, n, 1)), Mid(thestuff, n, 1), "")
End If
Next n
scrubomatic = strHold
End Function

 

[mkleino]

Thanks very much, worked like a charm!

-Mike