How many times the word appears in the cell

Discuss the spreadsheet application

How many times the word appears in the cell

Postby dawidek990 » Mon Jul 15, 2019 12:58 pm

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
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: How many times the word appears in the cell

Postby RusselB » Mon Jul 15, 2019 3:48 pm

OpenOffice 4.1.7 and LibreOffice on Windows 7 Pro & Ultimate
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
Posts: 5669
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How many times the word appears in the cell

Postby Lupp » Mon Jul 15, 2019 3:55 pm

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   Expand viewCollapse view
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2564
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How many times the word appears in the cell

Postby Zizi64 » Mon Jul 15, 2019 4:01 pm

A possible macro solution:

Code: Select all   Expand viewCollapse view
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
         Exit Do
      end if

Enumerate_Str1_in_Str2 = myCounter
End function

(11.05 KiB) Downloaded 7 times
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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.
User avatar
Posts: 8533
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Return to Calc

Who is online

Users browsing this forum: Google [Bot], RusselB and 42 guests