Il giorno venerdì 27 marzo 2015 15:24:55 UTC+1, GS ha scritto:
Il giorno venerdì 27 marzo 2015 13:03:47 UTC+1, Robert Crandal ha
scritto:
Suppose I have the following string.
Dim s as String
s = "Always eat lots of fruits and vegetables every day."
I would like to be able to truncate this string as quickly
as possible, beginning from a word of my choosing, all
the way to the end of the string.
So, if I choose the word "and" as my target word, the
above string should be transformed into:
"Always eat lots of fruits "
Essentially, all text after the first occurence of "and"
was removed from the string.
I'm looking for solution that runs quickly. Thanks!
Left$(k, InStr(1, k, f, vbTextCompare) - 1)
Given the OP's criteria is "..as quickly as possible,...", the Mid()
function is considerably faster!

' Windows 7/64 - Excel 2013
'
Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub Test()
Const DebugOn = False
Const k = "Always eat lots of fruits and vegetables every day."
Const f = "And"

Dim i As Long
Dim i1 As Long
Dim t As Long
Dim t1 As Long
Dim d As Long

Dim s As String

If DebugOn Then
t = 1
t1 = 1
Else
t = 100000
t1 = 5
End If

For i1 = 1 To t1

If DebugOn Then
Debug.Print "Split:", "'" & Split(k, f, Compa=vbTextCompare)(0) & "'"
Else
d = GetTickCount
For i = 1 To t
s = Split(k, f, Compa=vbTextCompare)(0)
Next
d = GetTickCount - d
Debug.Print Format$(d, "0") '
End If

If DebugOn Then
Debug.Print "Mid$:", "'" & Mid$(k, 1, InStr(1, k, f, vbTextCompare) - 1) & "'"
Else
d = GetTickCount
For i = 1 To t
s = Mid$(k, 1, InStr(1, k, f, vbTextCompare) - 1)
Next
d = GetTickCount - d
Debug.Print Format$(d, "0") '
End If

If DebugOn Then
Debug.Print "Left$:", "'" & Left$(k, InStr(1, k, f, vbTextCompare) - 1) & "'"
Else
d = GetTickCount
For i = 1 To t
s = Left$(k, InStr(1, k, f, vbTextCompare) - 1)
Next
d = GetTickCount - d
Debug.Print Format$(d, "0") '
End If

If DebugOn Then
Debug.Print "Mid:", "'" & Mid(k, 1, InStr(1, k, f, vbTextCompare) - 1) & "'"
Else
d = GetTickCount
For i = 1 To t
s = Mid(k, 1, InStr(1, k, f, vbTextCompare) - 1)
Next
d = GetTickCount - d
Debug.Print Format$(d, "0") '
End If

If DebugOn Then
Debug.Print "Left:", "'" & Left(k, InStr(1, k, f, vbTextCompare) - 1) & "'"
Else
d = GetTickCount
For i = 1 To t
s = Left(k, InStr(1, k, f, vbTextCompare) - 1)
Next
d = GetTickCount - d
Debug.Print Format$(d, "0") '
End If

Debug.Print

Next
End Sub

Split Mid$ Left$ Mid Left
187 109 109 109 110
218 109 94 109 125
203 93 93 110 109
218 94 109 109 125
187 109 109 109 110
187 94 109 109 109
187 109 109 110 109
187 94 109 109 110
187 109 109 109 110
187 110 93 109 110
187 109 94 125 109
187 109 94 124 110
187 109 94 124 110
172 94 109 109 110
187 94 109 109 109
187 93 110 109 109
187 109 94 124 110
187 109 94 109 125
187 110 93 109 110
187 109 94 109 125
Min : 172 93 93 109 109
Max : 218 110 110 125 125
Average : 190,15 103,75 101,40 112,15 112,70
Rank : 5 2 1 3 4
0,0000% -45,4378% -46,6737% -41,0202% -40,7310%