# code find txt2 in txt1

#### erik.van.geit

##### MrExcel MVP
Hi, gurus!

What's the starting position of txt2 found in txt1 ?

I can make code that loops through the text
Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "eFG"

For ch = 1 To Len(txt1) - Len(txt2)
If UCase(txt2) = UCase(Mid(txt1, ch, Len(txt2))) Then
Exit For
End If
Next ch
MsgBox "the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch
End Sub

but what's the "right VBA-tool" to do this ?
it is like the worksheetequivalent in the example below

kind regards,
Erik
DEFG
15EFGabcdEFGhijkEFGlm

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Oaktree

##### MrExcel MVP
Why not use application.worksheetfunction.find ?

#### just_jon

##### Legend
Howdy, Erik -- see the InStr function.

#### erik.van.geit

##### MrExcel MVP
Thank you,
Oaktree and just_jon,

I was trying FIND but didn't find the right syntax and thought that a "real VBA-solution" existed
Here the results
Code:
``````Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "eFG"

On Error Resume Next
ch = Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch

On Error Resume Next
ch = InStr(1, UCase(txt1), UCase(txt2))
If ch <> 0 Then MsgBox "USING : InStr(1, UCase(txt1), UCase(txt2))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch
On Error GoTo 0

End Sub``````

IMPORTANT REMARQUE
the worksheetfunction is much slower
I tested the code with 111111 loops
the Instrfunction was almost 7 times faster
Let's make a plea for the VBA-functions.
kind regards,
Erik

#### erik.van.geit

##### MrExcel MVP
here the proof
Code:
``````Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "efG"
sss = 111111

On Error Resume Next
Start = Timer
For x = 1 To sss
ch = Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))
Next
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch & Chr(10) & Chr(10) & _
"USED TIME (" & sss & "loops): " & Timer - Start

On Error Resume Next
Start = Timer
For x = 1 To sss
ch = InStr(1, UCase(txt1), UCase(txt2))
Next
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : InStr(1, UCase(txt1), UCase(txt2))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch & Chr(10) & Chr(10) & _
"USED TIME (" & sss & "loops): " & Timer - Start``````
End Sub

Replies
17
Views
376
Replies
17
Views
222
Replies
12
Views
316
Replies
9
Views
356
Replies
16
Views
186

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,119
Messages
5,768,221
Members
425,460
Latest member
Astros1243

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back