how to find string in another string by using vba only

Question

So I have

  Dim str1
  str1 = "Cat"
  Dim str2
  str2 = "concatenate"

I wanted to know is there a way I can match str1 with str2 and return positive non-zero number if there is match (case-insensitive) for str1 in str2 ?

This question has already been asked ! http://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string — titus, Sep 22 '15 at 20:59
Possible duplicate of [Check if a string contains another string](http://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string) — niton, Mar 18 '17 at 00:29

score 2 · Accepted Answer · answered Sep 22 '15 at 20:32

2

For VBA, The instr is the way to go:

InStr(1, str2, str1, vbTextCompare)

The first part is where it starts looking, in this case the first character.

The second is the string in which the search is happening.

The third is the search parameter.

The fourth determines whether it looks at the text of the binary value. So for a case-insensitive we use vbtextcompare.

answered Sep 22 '15 at 20:32

Scott Craner

148,073
10
49
81

will this work if str1 and str2 are arrays with strings and str1(i) compares for a match with all str2(j) ? – AriKari Sep 22 '15 at 20:33
No. There are many ways to do it, loops and dictionaries, depending on what is being searched. But that is not what you asked. – Scott Craner Sep 22 '15 at 20:36
which variable will return a positive value if match is found ? – AriKari Sep 22 '15 at 20:38
The result of the function that is being called will result in a positive number if found and a zero if not. So for your example the result would be 4. You would create a variable and set it = to the function above. – Scott Craner Sep 22 '15 at 20:41
To work with an array you have to loop in and compare each position. – WltrRpo Sep 22 '15 at 21:55

score 0 · Answer 2 · answered Sep 22 '15 at 20:21

0

Use the indexOf method:

str2.indexOf(str1)

answered Sep 22 '15 at 20:21

ergonaut

6,929
1
17
47

will this work if str1 and str2 are arrays with strings and str(i) compares for a match with all str(j) ? – AriKari Sep 22 '15 at 20:33
no, neither will any of the other answers. You'll need to loop. – ergonaut Sep 22 '15 at 20:35
Yeah sure I will have to loop but it will work for arrays too right – AriKari Sep 22 '15 at 20:35
indexing an array returns the object at a particular location. If that object is a string, it will act like one because it is one. – ergonaut Sep 22 '15 at 21:20

vacip · Answer 3 · 2015-09-22T21:27:36.873

0

Use instr or instrrev:

instr(str2, str1, 1)

See msdn for more info. This does exactly what you need.

edited Sep 22 '15 at 21:27

answered Sep 22 '15 at 20:24

vacip

5,246
2
26
54

I may be wrong but this is for vb not vba, which is what the OP asked. – Scott Craner Sep 22 '15 at 20:39

how to find string in another string by using vba only

3 Answers3