Notifications
Sign Up Sign In
Q&A

Count the number of occurrences in a text string

+0
−0

This Q from Binz Nakama at Stack Overflow interested me because of the Comment there:

OK, i think this would require a macro, AFAIK there's no single formula or combination to achieve this.

The Comment was from tohuwawohu, the user currently standing top of the All Time list of Answerers for [libreoffice-calc], the tag applied to the Q.

The essence of the Q was:

I'm looking for a formula or combination thereof that can count the number of occurrences/repetitions in a text string.

Examples provided in the Q were:

if cell A1 contains "XYZXYZ", I would like to search cell A1 for "XYZ" and have "2" as a result

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

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

Aside: "Tags must have at least one tag" is odd.

Why should this post be closed?

8 comments

Hi @Moshi. I would not like to have to use the long-form of question every single time on a Q&A site. Sorry, can't see what grammar you have changed. I'm not sure but I suspect a space between ] and ( breaks a link. ‭pnuts‭ 18 days ago

@pnuts It's a problem for me, who finds one letter long links to be difficult to see and click on. Also, I'm not holding you to anything, I just felt it would improve the post ‭Moshi‭ 18 days ago

Also, i didn't realize that my grammar checker added a space in there, oops. I usually turn it off when writing markdown ‭Moshi‭ 18 days ago

@Moshi I have included a summary of what I interpreted to be the original question so I hope no need to click on the [Q] - it was only there for attribution. Did you not also suggest expanding other (non-link) Qs? What about the grammar? ‭pnuts‭ 18 days ago

because of the Comment there -> a comment the user currently standing top of the All Time list -> on top of ‭Moshi‭ 18 days ago

Show 3 more comments

1 answer

+2
−0

Although spreadsheets were never really intended for text processing, I think a formula solution is possible (LibreOffice 5.4.3.2):

For the first example above:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"XYZ","")))/LEN("XYZ")

and for the second example:

=(LEN(B1)-LEN(SUBSTITUTE(B1,"Mary","")))/LEN("MARY")

However I would suggest a better approach may be to parameterise the relevant 'occurrence' as doing so simplifies the formula and displays the 'occurrence' while the formula is hidden.

Given the text string (only) in A1 and the 'occurrence' in B1, the formula for both examples could be:

=(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)

This formula could also be in at least Excel, Sheets and OpenOffice Calc, given the appropriate specific separator, which may be locale dependent.

The formula is case sensitive and works by subtracting from the length of the text string the length of the text string without 'occurrence/s' (by substituting each 'occurrence' with nothing) and then dividing the difference by the number of characters in each 'occurrence'.

A word of caution however, I have not tested this for every combination of alternative solutions (2), text string location (2), examples (2), case (2) and package (4).

1 comment

It's probably worth mentioning that overlapping occurences are not accounted for. For example, while "ABBA" occurs twice in "ABBABBA", the technique explained in the answer will only count one occurence. I am not saying that the intent of the asker included accounting for overlaps, but it is something to keep in mind when employing this technique. ‭elgonzo‭ 17 days ago

Sign up to answer this question »