How to pull certain words from a long list of “messages” and count how many instances of them are mentioned?

[uvarg002]

The column (I) in question has 4 phrases in one cell (I2). I want to be able to grab the information in column (H) using the phrases from column I.

For example. The words say assignment update, required document, assignment needed, document assigned. I want to be able to see how many times each instance is mentioned throughout column H. Just the word, not the entire message.

I tried conditional formatting > highlight cell rules > text that contains. Then added a filter for the highlighted ones and copied them to a pivot table. However, the pivot tables display the full text and not an actual number of how many instances it has been mentioned.

Excel version: 365, 2021.

 

[Anthony47]

You didnt specify if you are interested in a specific list of words or all the possible words in the text; I'll assume this second option.

Your Office 365 should include the functions SPLIT and LET, thus:
-the combined phrases are in I2
-insert in K2 the formula =SPLIT(SUBSTITUTE(I2,",","")," ")
Note that I AM NOT sure that the name of the function is SPLIT, in english
This will list in K2 and down all the words available in cell I2, using "comma" or "space" as separator. If there are not enough free cells in K3 and down then you will have the #SPILL! error

Now insert in L2 the formula =LET(iTxt,SUBSTITUTE($I$2,","," ")&" ",cWd,K2&" ",(LEN(iTxt)-LEN(SUBSTITUTE(iTxt,cWd,"")))/LEN(cWd))
This will return how many times the word in K2 is available in the initial text; copy down as needed

In case that you are interested only in a certain list of words, then insert the words in K2 and down, rather then using the formula in K2

Try...

 

[uvarg002]

You didnt specify if you are interested in a specific list of words or all the possible words in the text; I'll assume this second option.

Your Office 365 should include the functions SPLIT and LET, thus:
-the combined phrases are in I2
-insert in K2 the formula =SPLIT(SUBSTITUTE(I2,",","")," ")
Note that I AM NOT sure that the name of the function is SPLIT, in english
This will list in K2 and down all the words available in cell I2, using "comma" or "space" as separator. If there are not enough free cells in K3 and down then you will have the #SPILL! error

Now insert in L2 the formula =LET(iTxt,SUBSTITUTE($I$2,","," ")&" ",cWd,K2&" ",(LEN(iTxt)-LEN(SUBSTITUTE(iTxt,cWd,"")))/LEN(cWd))
This will return how many times the word in K2 is available in the initial text; copy down as needed

In case that you are interested only in a certain list of words, then insert the words in K2 and down, rather then using the formula in K2

Try...

Correct, I want all the possible words in the text. So "assignment update" I wanted to see how many times it was mentioned through column H. I thought a pivot table was needed but it's pulling out the entire text WITH the message.

The split and substitute does not seem to be working. I admit I am not sure what to fill in the the comma after the I2.

 

[Anthony47]

I have Office 365, (Version 2204 Build 16.0.15128.20210), no Office Insider option (ie standard user) and SPLIT has been distribuited recently; LET is available since months.
If you have an English version, you gave to use the formula as suggested; if you use a different language then you need to translate the name of the Functions in your local language

 

[Anthony47]

Let's forget about SPLIT, that could not yet be available in your Office (it depends on the updating channel)
-the combined phrases are in I2
-insert in K2 the formula

'=UNIQUE(LET(txt,SUBSTITUTE(I2,","," "),sep," ",( FILTERXML("<atag><btag>"&SUBSTITUTE(TRIM(RIGHT("00.00.",MAX(0,7-LEN(txt)))&txt),sep,"</btag><btag>")&"</btag></atag>","//btag"))))

This will list in K2 and down all the words available in cell I2, using "comma" or "space" as separator. Again, if there are not enough free cells in K3 and down then you will have the #SPILL! error

-set in L2 the same formula given before:

=LET(iTxt,SUBSTITUTE($I$2,","," ")&" ",cWd,K2&" ",(LEN(iTxt)-LEN(SUBSTITUTE(iTxt,cWd,"")))/LEN(cWd))

We use LET, that was released mid of 2021, and thus should be available even to users set on the semiannual update channel.