• 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

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

navic

Active Member
Try ARRAY formula in C2 for case sensitive (copy down)
Code:
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
 

Juniad

Member
Try ARRAY formula in C2 for case sensitive (copy down)
Code:
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
It is working even without array formula. . no need to hit combination of keys. BTW thank a lot. :):):)
 

navic

Active Member
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.
 

bosco_yip

Excel Ninja
Try ARRAY formula in C2 for case sensitive (copy down)
Code:
=SUM((LEN($D$2)-LEN(SUBSTITUTE(UPPER($D$2),UPPER(B2),"")))/LEN(B2))
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:

Juniad

Member
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
Yes you are right, How we can avoid such problem ?
 

GraH - Guido

Well-Known Member
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

GraH - Guido

Well-Known Member
If there is a challenge, we can just wait for Bosco to solve even the impossible. You are making us lazy (and humble) ;-)
 

DavidBateman

New 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 UK Edubirdie list how many time used in essay.
example workbook attached herewith.
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:

Peter Bartholomew

Well-Known Member
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)))
          )
      )
  )
 

deciog

Active Member
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
 

GraH - Guido

Well-Known Member
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?
 

deciog

Active Member
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.

Decio
 

Peter Bartholomew

Well-Known Member
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

deciog

Active Member
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
 
Top