# Find out the word how many time used in essay

##### 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

• 9 KB Views: 11

#### 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))``

• ##### 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:
• ##### 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à..."

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

#### Attachments

• 20.8 KB Views: 4
• Thomas Kuriakose and navic

#### bosco_yip

##### Excel Ninja
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

• 22.9 KB Views: 7

#### 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:

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?

• Peter Bartholomew

#### 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

• 17.1 KB Views: 1
• deciog

#### 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