Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the appearance of a String in a Row | Excel Worksheet Functions | |||
macro | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
counting instances of words in a worksheet cell | Excel Discussion (Misc queries) |