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
dawidek990
 
Posts: 33
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.6 and LibreOffice 6.0.6.2 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
RusselB
Moderator
 
Posts: 5296
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
=(LEN(A2)-LEN(SUBSTITUTE(A2;$A$1:"")))/LEN($A$1)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2523
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
      else
         Exit Do
      end if
   Loop

Enumerate_Str1_in_Str2 = myCounter
End function


Enumerate_Str1_in_Str2.ods
(11.05 KiB) Downloaded 2 times
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; 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
Zizi64
Volunteer
 
Posts: 8145
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 20 guests