| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of times a specific word appears in a single cell | Excel Discussion (Misc queries) | |||
| Finding the number of times a word is used in a range of cells | Excel Worksheet Functions | |||
| Finding Minimum but if same number repeats in the range, then find | Excel Discussion (Misc queries) | |||
| finding a number and the number of times it occurs | Excel Discussion (Misc queries) | |||
| How can I count the number of times a letter repeats in a string? | Excel Worksheet Functions | |||
Linear Mode
