Macro for finding number of times a word repeats in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am a very amateur excel user. Can you help me with a macro to find a
particular word/phrase from the spreadsheet and give an output on the number
of times a word/phrase is repeated in a different cell
E.g If Cell B5 has the word "This spreadsheet contains information on basic
excel topics and advanced excel topics". A macro should find that the phrase
"excel topics" had been repeated twice in cell B5 of the spreadsheet. This
number should be printed in cell C5.
 
Hi

Look in Help for the CountIf function

In a cell you use this
=COUNTIF(A1:A33,"ron")

You can use it in VBA like this

MsgBox Application.WorksheetFunction.CountIf(Range("A1:A33"), "ron")
 
=(LEN(B5)-LEN(SUBSTITUTE(LOWER(B5),"excel topics","")))/LEN("excel topics")

likewise in VBA

Dim s as String, s1 as String, cnt as Long
s = lcase("excel topics")
s1 = lcase(Range("B5").Value)
cnt = (len(s1)-len(replace(s1,s,"")))/len(s)
Range("C5").Value = cnt
 
Back
Top