Pulling number from string

Meltdown

Registered User.
Local time
Today, 21:38
Joined
Feb 25, 2002
Messages
472
Hi all,

How would I pull the first occurance of a number from a string, the string is variable length.

Regards
Melt
 
You can use this function (put it in a standard module and don't name the module the same as the function):

Code:
Function GetFirstNum(strInput As Variant) As Integer
 Dim i As Integer

 For i = 1 To Len(strInput)
    If IsNumeric(Mid(strInput, i, 1)) Then
        GetFirstNum = Mid(strInput, i, 1)
        Exit Function
    End If
Next i
End Function

Then you can call it in a query by using

MyNewFieldnameHere:GetFirstNum([YourFieldName])
 
Meltdown,


Code:
Public Function GetFirstNumber(SomeString As String) As Integer
Dim i As Integer
'
' Return first numeric character, or -1 if none
'
GetFirstNumber = -1
If Len(SomeString) = 0 Then Exit Function
For i = 1 To Len(SomeString)
   If Mid(SomeString, i, 1) >= "0" And Mid(SomeString, i, 1) <= "9" Then
      GetFirstNumber = Mid(SomeString, i, 1)
      Exit Function
   End If
   Next i

End Function

hth,
Wayne
 
... OR, use what SOS posted (that was quick!).

I do think you have to check for the zero-length-string though, otherwise
the loop will error.

Wayne
 
Good part Wayne about handling if a field has nothing. However, would it be better to use SomeString As Variant to avoid having to use an NZ function outside of it? I had thought about that after posting but I couldn't think of what to return if null.
 
SOS,

Good point. I really should have made it a variant.

See ya,
Wayne
 
  • Like
Reactions: SOS
Thanks very much guys, great answers, problem solved.

Regards
Melt
 

Users who are viewing this thread

Back
Top Bottom