Helmut Weber said:
Hi Jonathan,
I wonder, as I am not too familiar with "like",
whether one coudn't utilize "like", like ;-)
Dim s As String
s = "aret274scg"
If Mid(s, 5, 3) Like "###" Then
MsgBox "found"
End If
Using Like in this manner is better than using IsNumeric, because as you
pointed out, IsNumeric would return True for a string such as "1.0"
This can be combined with the idea of checking every 3rd character. We can
also make a function out of it, which returns the character position of the
3-digit number, or zero if no number is found. Finally, it can be extended
to include an optional starting position parameter. The function would then
look like this
Function FindFirst3Digits(strIn As String, _
Optional ByVal iStart As Long = 1) As Long
Dim i As Long
Dim j As Long
If iStart < 1 Then
iStart = 1
End If
j = 0
For i = iStart + 2 To Len(strIn) Step 3
If Mid$(strIn, i, 1) Like "#" Then
' found a digit
If Mid$(strIn, i - 1, 1) Like "#" Then
' char before is also a digit
If Mid$(strIn, i - 2, 1) Like "#" Then
' 2 chars before also a digit
j = i - 2
Exit For
' found 3 digit number starting at i-2
ElseIf Mid$(strIn, i - 1, 3) Like "###" Then
j = i - 1
Exit For
' found 3 digit number starting at i-1
End If
ElseIf Mid$(strIn, i, 3) Like "###" Then
j = i
Exit For
' found 3 digit number starting at i
End If
End If
Next i
FindFirst3Digits = j
End Function
If you simply check each character position in turn for the presence of a
3-digit string, you get code that is simpler, but much slower
Function FindFirst3DigitsAlt(strIn As String, _
Optional ByVal iStart As Long = 1) As Long
Dim i As Long
If iStart < 1 Then
iStart = 1
End If
For i = iStart To Len(strIn) - 3
If Mid$(strIn, i, 3) Like "###" Then
Exit For
' found 3 digit number starting at i
End If
Next i
FindFirst3DigitsAlt = i
End Function
You can easily check the relative timings of these two versions of the
function with the following code, which should be placed in a separate
module
Public Declare Function GetTickCount Lib "kernel32" () As Long
Sub TimeFunctions()
Dim tStart As Long
Dim tEnd As Long
Dim strIn As String
Dim iCount As Long
strIn = Space$(5000000) & "123"
tStart = GetTickCount
iCount = FindFirst3Digits(strIn)
tEnd = GetTickCount - tStart
Debug.Print "FindFirst3Digits", iCount, tEnd
tStart = GetTickCount
iCount = FindFirst3DigitsAlt(strIn)
tEnd = GetTickCount - tStart
Debug.Print "FindFirst3DigitsAlt", iCount, tEnd
End Sub
This code creates a 5000000 character string and appends "123" to the end.
It then applies both versions of the function to the string and prints the
result in the immediate window. The call to GetTickCount retrieves the value
of a timer that is incremented every millisecond, so the time that is
displayed is the elapsed time in milliseconds. On my test runs,
FindFirst3Digits is almost exactly 3 times faster than FindFirst3DigitsAlt.
