Search text & record number of times it appears
 

Dear All,
I need to search & record the number times the text appears in a
paragraph. I need to use the Find function (in my scenario) Any help
out there?

Thank you.

Regards,
Julian

Search text & record number of times it appears
 

Where is your paragraph stored? If it is all in one cell eg A1 then
enter the text you are serching for in B1 and then in any cell enter the
formula:
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

Hope this helps
Rowan

wrote:
Dear All,
I need to search & record the number times the text appears in a
paragraph. I need to use the Find function (in my scenario) Any help
out there?

Thank you.

Regards,
Julian

Search text & record number of times it appears
 

Hi, the text may appear in several cells in the sheet. That is why I
need to use the FIND function.

Regards,
Julian

Rowan wrote:
Where is your paragraph stored? If it is all in one cell eg A1 then
enter the text you are serching for in B1 and then in any cell enter the
formula:
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

Hope this helps
Rowan

wrote:
Dear All,
I need to search & record the number times the text appears in a
paragraph. I need to use the Find function (in my scenario) Any help
out there?

Thank you.

Regards,
Julian

Search text & record number of times it appears
 

Maybe like this but it will only find the number of cells that contain
the text, so if you have one cell that contains the text 5 times that
will only be counted as 1.

Sub FindAll()
Dim Found As Integer
Dim c As Range
Dim firstAddress As String
Const findText As String = "dog" 'text to find

With Worksheets(1).Range("a1:f500") 'set range and sheet
Set c = .Find(findText, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Found = Found + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
MsgBox "Found " & Found & " instances of " & findText
End Sub

Regards
Rowan

wrote:
Hi, the text may appear in several cells in the sheet. That is why I
need to use the FIND function.

Regards,
Julian

Rowan wrote:

Where is your paragraph stored? If it is all in one cell eg A1 then
enter the text you are serching for in B1 and then in any cell enter the
formula:
=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

Hope this helps
Rowan

wrote:

Dear All,
I need to search & record the number times the text appears in a
paragraph. I need to use the Find function (in my scenario) Any help
out there?

Thank you.

Regards,
Julian

Search text & record number of times it appears
 

That was quick. Worked like a charm. Thanks a million

regards,
Julian

Search text & record number of times it appears
 

You're welcome.

wrote:
That was quick. Worked like a charm. Thanks a million

regards,
Julian

Search text & record number of times it appears
 

Here's a shorter version


Sub CountCells()
Dim findText as String, found as Long
findText = "dog"
Found = application.Countif(worksheets(1).Range("A1:Z26"), _
"*" & findText & "*")
MsgBox "Found " & Found & " instances of " & findText
End sub

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
That was quick. Worked like a charm. Thanks a million

regards,
Julian