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)
Hello @luber
Please edit your question and attach the spreadsheet.
Kind regards, Michel
Thanks for clearly describing what
<|>
and,
means.Sorry, can't paste a spreadsheet segment <|> is a cell break , is just a random comma. Probably 10 cells etc is likely to be easiest while I make up a new spreadsheet. Thanks for the great ideas. Lu
Should "hillbilly" be found as an occurrence of "Bill"? If not, in what way must the substrings to find be delimited?
Can you assure that the substrings to search for not contain any reserved characters concerning RegEx?
Information of this kind is substantial if you want to get suggested reliable solutions.
Hello @luber1
Could you be also more specific about what went wrong ? Edit your question and provide the data set, the related result and the expected result.
@luber1, Don't forget to check the mark (
) to the left of the answer that solves your question.
Hello @luber1
In the newly appended file, the formula close to the solution is in
F3=COUNTIF(B2:E9;".*(B|b)ill.* " )
. Just remove the two white spaces : the first one between final*
and"
and the second one between"
and)
The right formula is
=COUNTIF(B2:E9;".*(B|b)ill.*")
Kind regards, Michel
The newly appended file actually was made with the "wildcard" setting under
>Tools>Options>LibreOffice Calc>Calculate>>Formulae wildcards
It must be changed to
Enable regular expressins in formulae
.(Wildcards are by far not powerful enough to do real work. They are just promoted by MS.)
With sufficiently recent versions you may use the switch
(?i)
to make the search case insensitive independent of the global setting. As opposed to the already suggested way this will apply, however, not to just one character, but to all characters following, as long as not a(?-i)
switches back.You may also take the opportunity to learn about Regex, and about a few additional techniques the playful way. I will post a respective attachment Imade "just for fun" to an otherwise probably obsolete answer.