Just 4fun,
here is a macro function solution. Just place it into Tools > Macros … > Edit Macros
and you can call it like any other calc function via:
=CMIR("Bill",B2:D10)
- 1st argument is the string to search for (case insensitiv)
- 2nd is a selected cell range
- Return value is the count of found matches
Here the macro function code:
rem (c)ount (m)atches (i)n (r)ange
function CMIR(needle as string, data() as array) as integer
count=0
for i = lbound(data,1)to ubound(data,1)
for j = lbound(data,2)to ubound(data,2)
parts() = Split(lcase(data(i,j)), lcase(needle))
blub = UBound(parts()) - LBound(parts())
if ( blub > 0 ) then
count=count+blub
endif
next
next
CMIR=count
end function
— update —
As promised, here is the regex version (2v1) had to fix some bugs sorry
usage:
=CMIR2("\b[B|b][Ii][Ll][Ll]\b",B2:D9)
code:
rem (c)ount (m)atches (i)n (r)ange version2v1
function CMIR2(needle as string, data() as array) as integer
Dim oTextSearch ' TextSearch service.
Dim sStrToSearch As String ' String to search.
Dim aSearchResult ' com.sun.star.util.SearchResult
Dim aSrcOpt As New com.sun.star.util.SearchOptions
Dim enLocale As New com.sun.star.lang.Locale
Dim rank As Long
Dim count as long
enLocale.Language = "en"
enLocale.Country = "US"
oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
With aSrcOpt
'https://api.libreoffice.org/docs/idl/ref/SearchFlags_8idl.html
.searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED
.Locale = enLocale
'Supports ABSOLUTE, REGEXP, and APPROXIMATE
.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
.searchString = needle 'rem define word bounds
'.transliterateFlags = com.sun.star.i18n.TransliterationModulesNew.UPPERCASE_LOWERCASE
End With
oTextSearch.setOptions(aSrcOpt)
count=0
rank = 0
for i = lbound(data,1)to ubound(data,1)
for j = lbound(data,2)to ubound(data,2)
sStrToSearch = data(i,j)
aSearchResult = oTextSearch.searchForward(sStrToSearch, 0, Len(sStrToSearch) )
rank=0
Do While aSearchResult.subRegExpressions > 0
count=count+1
sStrToSearch = mid(sStrToSearch, aSearchResult.endOffset(0) +1 , Len(sStrToSearch))
aSearchResult = oTextSearch.searchForward(sStrToSearch, 0, Len(sStrToSearch) )
Loop
next
next
CMIR2=count
end function
This Function can handel the hillbilly problem very gracefully. By using word boundarys \b
Hope it helps (even more)
) to the left of the answer that solves your question.