'LibreOffice Calc - Number of occurrences in text string

I'm sorry if this has been asked already, but I haven't found the answer.

I'm looking for a formula or combination thereof that can count the number of occurrences/repetitions in a text string. For example, if cell A1 contains "XYZXYZ", I would like to search cell A1 for "XYZ" and have "2" as a result (because "XYZ" occurs 2 times in A1).

={find.repetitions}(A1;"XYZ") --> 2

If B1 contains "Mary is my friend but Mary doesn't like roses":

={find.repetitions}(B1;"Mary") --> 2

I tried fiddling with the FIND and SEARCH functions, but they only give me the position of the first occurence. The COUNTIF function can only count entire matching cells within a range, which is not what I'm looking for. I would specifically like to count occurrences of a text snippet within a single cell (single text string).

Any help is greatly appreciated!

libreoffice-calc


Solution 1:[1]

A friend of mine came up with the answer!

If A1 contains "XYZ how many times does XYZ occur XYZ?" and A2 contains "XYZ", then the function

=(len(A1)-substitute(A1;A2;""))/len(A2)

will produce 3.

(question may be closed, I do not have enough points to do so).

Solution 2:[2]

The correct formula would be:

=(len(A1)-len(substitute(A1;A2;"")))/len(A2)

And it works perfectly

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Binz Nakama
Solution 2 Michele Falappi

Related Questions