Instr

Peterso

Board Regular
Joined
Nov 28, 2012
Messages
90
I know "Instr" can help me to find out whether a particular string exits. Can it also tell how many of that particular string appear in a text?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I know "Instr" can help me to find out whether a particular string exits. Can it also tell how many of that particular string appear in a text?

You would need to loop through the entire text (using Instr) to determine all position matches eg:

Code:
strMyString = "the string here will be used to count the number of thes"   'so 3 occurrences of the

strMAtch = "the"

cnt = 0

posn = InStr(strMyString, strMAtch)

If posn > 0 Then   'there's at least one strMatch in strMyString
  Do
     cnt = cnt + 1
     posn = InStr(posn + 1, strMyString, strMAtch)
  Loop While posn <> 0
End If

MsgBox "There are " & cnt & " instance(s) of " & strMAtch & " within """ & strMyString & """"
 
Upvote 0

Forum statistics

Threads
1,203,068
Messages
6,053,346
Members
444,654
Latest member
Rich Cohen

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top