extrating a number

[Sam1982]

Hi!!

I need to know if there is a way to extract a number from some text in a table??

for example if there is a field in a table with comments in it but these comments always had a number in such as

"add 1 to batch" i would like to extract the "1" from that sentece.

Is this possible

I have tried the Var funtion but that doesn't seem to work as it only removes the spaces

thanks for your help

Sam

 

[charityg]

I have written a function that extracts a numeric string from a passed in string.

Function getNumber(strIN As String) As Integer
Dim strOut As String
Dim i As Integer
Dim sLen As Integer

strOut = ""
For i = 1 To Len(strIN)
If IsNumeric(Mid(strIN, i, 1)) Then
strOut = strOut & Mid(strIN, i, 1)
End If
Next i
getNumber = strOut
End Function

See if this works for you.

 

[The_Doc_Man]

If the format of the passed-in string is fixed, you can do it in a simplistic way with a Mid function. On the other hand, if your passed-in string is totally arbitrary, you need a parser. It is well beyond the scope of normal Access abilities to parse arbitrary text, though that doesn't mean it is impossible with appropriate VBA code.

 

[charityg]

Huh?

 

[IgorB]

Private Function PurgeNumericInput(StringVal As Variant) As Variant

On Local Error Resume Next
Dim x As Integer
Dim WorkString As String

If Len(Trim(StringVal)) = 0 Then Exit Function

For x = 1 To Len(StringVal)
Select Case Mid(StringVal, x, 1)
Case "0" To "9"
WorkString = WorkString + Mid(StringVal, x, 1)
End Select
Next x

PurgeNumericInput = WorkString

End Function