counting appearances of a short string within strings in a whole worksheet

[ricowyder]

Hi group members,

After copying a large amount of text from a pdf (I unfortunately have
no other way to import those data), I need to count the appearances of
the string "mrs" in the whole worksheet. After trying several options,
I am desperate and would like to ask you for help.

Imagine all data togethe in a cell (all text, sometimes figures).
I now want to count the string "mrs" within this string, but not only
in one cell, but in all cells.

Thanks a lot, if you would help me with this.

Cheers,

Rico

 

[Jim Rech]

You'd need a macro. See if this does it:

Sub TestIt()
Dim StrCount As Long
StrCount = CountSubStrings("mrs", Cells)
MsgBox StrCount
End Sub

Function CountSubStrings(Strg As String, Rg As Range) As Long
Dim Cell As Range
Dim StrLeng As Integer
Dim CellCount As Long, TotalCount As Long
Dim CellVal As String, ShortStr As String
StrLeng = Len(Strg)
On Error GoTo ExitThis
For Each Cell In Rg.SpecialCells(xlCellTypeConstants, xlTextValues)
CellVal = Cell.Value
ShortStr = Replace(CellVal, Strg, "", , , vbTextCompare)
CellCount = (Len(CellVal) - Len(ShortStr)) / StrLeng
TotalCount = TotalCount + CellCount
Next
CountSubStrings = TotalCount
ExitThis:
End Function


--
Jim
| Hi group members,
|
| After copying a large amount of text from a pdf (I unfortunately have
| no other way to import those data), I need to count the appearances of
| the string "mrs" in the whole worksheet. After trying several options,
| I am desperate and would like to ask you for help.
|
| Imagine all data togethe in a cell (all text, sometimes figures).
| I now want to count the string "mrs" within this string, but not only
| in one cell, but in all cells.
|
| Thanks a lot, if you would help me with this.
|
| Cheers,
|
| Rico
|

 

[ricowyder]

Thanks Jim. Exactly what I needed. Works perfect.

Regards,

Rico