How many times the word appears in the cell

Discuss the spreadsheet application
Post Reply
dawidek990
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

How many times the word appears in the cell

Post by dawidek990 »

Hi, I need a formula that will show me how many times the word appears in the cell
<li>nożyczki plastikowe</li><li>szczypce</li><li>strzykawka</li><li>termometr</li><li>otoskop</li><li>młoteczek</li>
I need to know how many times <li> is in this cell, and how many times </ li>.
OpenOffice 4.1.6 Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How many times the word appears in the cell

Post by RusselB »

Similar request and solution at viewtopic.php?f=9&t=74058
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How many times the word appears in the cell

Post by Lupp »

Suppose you want to count the number of occurrences of that text from cell A1 in the text contained in cell A2 and to get the result shown in cell B2.
Put the formula

Code: Select all

=(LEN(A2)-LEN(SUBSTITUTE(A2;$A$1:"")))/LEN($A$1)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How many times the word appears in the cell

Post by Zizi64 »

A possible macro solution:

Code: Select all

REM  *****  BASIC  *****
option Explicit

Function Enumerate_Str1_in_Str2(Str1 as string, Str2 as string) as long
 
 Dim LenStr1 as long
 Dim LenStr2 as long
 Dim myCounter as integer
 Dim ActPos as integer
 Dim SearchFrom as integer
 
	LenStr1 = len(Str1)
	LenStr2 = len(Str2)
	myCounter = 0
	SearchFrom = 1
	
	Do While SearchFrom < LenStr2
		ActPos = InStr(SearchFrom, Str2, Str1, 1)
		If ActPos <> 0 then			 
			myCounter = myCounter + 1
			SearchFrom = ActPos + LenStr1
		else
			Exit Do
		end if
	Loop

Enumerate_Str1_in_Str2 = myCounter
End function
Enumerate_Str1_in_Str2.ods
(11.05 KiB) Downloaded 65 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply