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

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.

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

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:A3 3"), "ron")




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Vineeth" wrote in message ...
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.

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

=(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

--
Regards,
Tom Ogilvy


"Vineeth" wrote:

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.

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

Oops, see Tom's reply

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
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:A3 3"), "ron")




--
Regards Ron de Bruin
http://www.rondebruin.nl



"Vineeth" wrote in message ...
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.