Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics
Linux Systems
Linux Systems
Power Users
Power Users

Dashboard
Notifications
Mark all as read
Q&A

Count the number of occurrences in a text string

+1
−1

The question about LibreOffice Calc - Number of occurrences in text string from Binz Nakama at Stack Overflow interested me because of one of the comments posted 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 at 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 does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

7 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‭ 10 months 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‭ 10 months 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‭ 10 months 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‭ 10 months ago

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

Show 2 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).

Why does this post require moderator attention?
You might want to add some details to your flag.

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‭ 10 months ago

Sign up to answer this question »