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

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

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

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
"ricowyder" wrote in message
oups.com...
| 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
|

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

Do you need to count the number of cells containing "mrs" or every occurences
or "mrs", counting each occurences in every cell ?
--
Regards,
Luc.

"Festina Lente"


"ricowyder" wrote:

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

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

Thanks Jim. Exactly what I needed. Works perfect.

Regards,

Rico

Jim Rech wrote:
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
"ricowyder" wrote in message
oups.com...
| 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
|