• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find out the word how many time used in essay

Juniad

Juniad

Active Member
Hello experts,
i have list of some words, and i have wrote an essay in another cell where i want to see the words in list how many time used in essay.

example workbook attached herewith.
 

Attachments

  • Example book.xlsx
    9 KB · Views: 14
Try ARRAY formula in C2 for case sensitive (copy down)
Code: 
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
 
navic said:
Try ARRAY formula in C2 for case sensitive (copy down)
Code: 
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
Click to expand...
It is working even without array formula. . no need to hit combination of keys. BTW thank a lot. :):):)
 
You're welcome
BTW: You do not need an array formula if you have one cell. But if you have multiple cells you need a formula array.
 
navic said:
Try ARRAY formula in C2 for case sensitive (copy down)
Code: 
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
Click to expand...
Assume your formula put in Column C and copied down, and some problems appeared as in :

1] In C4 formula result =1, then, "jump" is equal to "jumps" ???

2] In C12 formula result =2, then, "the" is equal to "there" ??? ( there is only 1 "the")

Regards
Bosco
 
Last edited:
bosco_yip said:
Assume your formula put in Column C and copied down, and some problems appeared as in :

1] In C4 formula result =1, then, "jump" is equal to "jumps" ???

2] In C12 formula result =2, then, "the" is equal to "there" ??? ( there is only 1 "the")

Regards
Bosco
Click to expand...
Yes you are right, How we can avoid such problem ?
 
Power Query to the rescue?
1. Load Essay as a table -> remove line feeds, proper case text, remove punctuations, group by row count
2. Load Words to Count as table -> left outer join with previous result, expand table, "et voilà..."

[Edit]: with an added index, you can keep the same order of your initial list of words to count.
 

Attachments

  • count words in text.xlsx
    20.8 KB · Views: 4
Here's is a formula solution

1] Using GraH - Guido's file for formula and PQ result comparison

2] Formula result put in column J, in J2 formula copied down :

=(LEN(SUBSTITUTE(" "&D$2&" ",".",""))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(" "&D$2&" "),".",""),CHAR(10)," ")," "&B2&" ","")))/LEN(" "&B2&" ")

Regards
Bosco
 

Attachments

  • count words in text(BY).xlsx
    22.9 KB · Views: 9
If there is a challenge, we can just wait for Bosco to solve even the impossible. You are making us lazy (and humble) ;-)
 
Juniad said:
Hello experts,
i have list of some words, and i have wrote an essay in another cell where i want to see the words in UK Edubirdie list how many time used in essay.
example workbook attached herewith.
Click to expand...

Hello from 2022, this is still helpful, thank you)
Moderator note:
Please DavidBateman, do not modify others writings and add links to somewhere.
 
Last edited by a moderator:
Future option only

Code: 
= WordCountλ(text,list)

= LAMBDA(txt,lst,
      LET(
          k,     SEQUENCE(LEN(" "&txt)),
          break, IF(CODE(MID(txt,k,1))=32,k,""),
          start, FILTER(break,ISNUMBER(break)),
          end,   SEARCH(" ",txt&" ",start+1),
          words, TRIM(MID(txt,start,end-start)),
          MAP(lst,
              LAMBDA(lw, SUM(N(words=lw)))
          )
      )
  )
 
Peter Bartholomew, Good Morning.

So that I can understand and test your formula, I need you to post a spreadsheet with the formulas, I am not able to assemble the formula, my level is much lower

hugs

Decio
 
Deciog,

The lambda formula must be in the name manager with the name "WordCountλ".
Then you can invoke the function "WordCountλ" on the cell, feeding it the parameters (cell references) for text and list.
This is how you tried it?
 
Guido, good afternoon, Thanks for helping

Yes, I tried but here it is giving an error, when Peter posts a model it is more facial to understand because the formula works and is automatically translated

I did the translation in the best formala translator I know.
en.excel-translator.de

Translator

The online tool for the translation of Excel formulas and functions from one language to another. Supports the replacement of argument separators, 35 languages and much more.
en.excel-translator.de en.excel-translator.de

Decio
 
Sorry @deciog. I posted more to show the type of change that has been made to Excel than to provide a working example. To get the correct result, I also had to remove the punctuation marks from the original, otherwise 'fox' and 'fox.' are different words. That needs a further Lambda function
Code: 
= REDUCE(sourceText, punctuation, replaceMarkλ)

replaceMarkλ
= LAMBDA(txt,chr, SUBSTITUTE(txt, chr, spc))
A possible goal is to program solutions with Lambda functions being used to implement all significant areas of functionality. Solution development then becomes a full-on programming exercise, rather than that of traditional 'spreadsheet authoring'. It won't suit everyone, but hopefully it means that more rigorous software development practices could be adopted where needed.
 

Attachments

  • Count words in text (PB).xlsx
    17.1 KB · Views: 5
Peter, Good morning.

I'm sorry, because I couldn't assemble the formula and give the result, but in this model I really appreciate it because I learned one more and now there was a correct translation and it worked correctly.

Thank you very much

Decio
 
This formula doesn't work for me, where should it be inserted? Because I have the impression that I don't put it correctly but I don't understand why I don't do it this way.
 
Cecilrodgers, Good morning.

Check your version of Excel because it only works on version 365 2021 or higher

Decio
 
If you have Excel 365, you go to the Name Manager dialogue and replaceMarkλ is entered into the 'Name' box whilst the formula, starting with =, goes into the 'Refers to' box. As always with defined names, the same result would be obtained by nesting the definition within formulas in place of referring to them by name, just not as tidy or readable.
 
You must log in or register to reply here.
Back
Top