I am trying the return the location of the first number in a text.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to return the location of the first number in a text string, so I
can use this as the starting point for my 'mid' function return. I am hoping
to use a wildcard or generic number search, since the first number could be
any from 0 to 9.
 
DarrelS said:
I am trying to return the location of the first number in a text string, so I
can use this as the starting point for my 'mid' function return. I am hoping
to use a wildcard or generic number search, since the first number could be
any from 0 to 9.
Click to expand...


Sorry, you'll have to code this functionality yourself
(using Mid in a loop). Wildcards require the use of Like,
which returns True or False, not a position.
 
You can use the following function in your query.

Function GetFirstNumber(MyField) As Integer
Dim x As Integer
Dim v_NextChar As Variant
For x = 1 To Len(MyField)
v_NextChar = Mid(MyField, x, 1)
If IsNumeric(v_NextChar) Then
GetFirstNumber = InStr(MyField, v_NextChar)
Exit Function
End If
Next x
GetFirstNumber = 0
End Function
 
Try this function:

Function GetFirstNumber(MyField) As Integer
Dim x As Integer
Dim v_NextChar As Variant
For x = 1 To Len(MyField)
v_NextChar = Mid(MyField, x, 1)
If IsNumeric(v_NextChar) Then
GetFirstNumber = InStr(MyField, v_NextChar)
Exit Function
End If
Next x
GetFirstNumber = 0

End Function
 
Lynn,

Why not just use
GetFirstNumber = X
instead of
GetFirstNumber = InStr(MyField, v_NextChar)

I'm serious. I just want to know if I'm missing something.
 
Thanks so much. After the first response, I went back and, with the help of
some colleagues, wrote the following, which works as well:

Public Function FirstNum(NumStr)
Dim i As Integer
For i = 1 To Len(NumStr)
If Val(Mid(NumStr, i, 1)) > 0 And Val(Mid(NumStr, i, 1)) < 10 Or
Mid(NumStr, i, 1) = 0 Then FirstNum = i: Exit Function
Next i
End Function
 
That works as well. Thanks.

John Spencer (MVP) said:
Lynn,

Why not just use
GetFirstNumber = X
instead of
GetFirstNumber = InStr(MyField, v_NextChar)

I'm serious. I just want to know if I'm missing something.
Click to expand...
 
Lynn,
Why not just use
GetFirstNumber = X
instead of
GetFirstNumber = InStr(MyField, v_NextChar)
Click to expand...

Yeah, Why not? I wrote that on the fly and was probably being way too
technical when I did it. Thanks, John.
 
Back
Top