# Count actual word in cell

• Hello all!

I am using an array formula to seek, within cells of each sheet, whatever word I type in to cell K2 in my workbook. Here's the formula:

{=SUM(LEN(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8)))-LEN(SUBSTITUTE(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8)),UPPER(\$K\$2),"")))/LEN(\$K\$2)}

Let's say I am want to look up the word "Pick". The above formula works great BUT, I am ONLY interested in finding the word "Pick", not "PickING" or "PickED" or "PickLED" which the above will find and include. I have been fiddling with different portions of the formula so it only selects the word "Pick" (in my example) and disregard all others; adding parameters to the formula to "look" for spaces pre/post the search word?

Thank you all so much!

• Re: Count actual word in cell

If you can accept a user defined function solution, then I found some regular expression code.
There's also a spreadsheet with the code installed and working.

## Files

Bruce :cool:

• Re: Count actual word in cell

Thank you skywriter! That is very generous of you to include a sample workbook. I will play around with the UDF and the array formula and post back an update.

• Re: Count actual word in cell

Hi skywriter -

Again, thank you for the UDF! Unfortunately, I am not able to work it into an array formula. I've tried several combinations to no avail. The formula I shared in the original post looks across all sheets and returns an answer albeit not the wanted one. Perhaps I am missing something very obvious. Any thoughts?

• Re: Count actual word in cell

It's not my code.

I've done all I can for you. :cheers:

Bruce :cool:

• Re: Count actual word in cell

Sounds good. Thank you.

• Re: Count actual word in cell

[sw]*[/sw]

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count actual word in cell

I apologize KjBox, I just saw your reply. Thank you much for offering further assistance. I have tried multiple iterations to no avail - yet.

The array formula resides in the "Stats" sheet under the "Occurrences of the word" table column with the lookup word located in cell K2. I have already added skywriter's UDF to the WB.

I am having problems uploading to Ozgrid at the moment will try in a bit.

Thanks again KjBox!

• Re: Count actual word in cell

Hi KjBox -

I have tried IE, FF and Chrome to no avail in my attempts to upload the file. I have followed the directions you provided to the T. I have tried "Drag-n-Drop" only to have the file open in my "Downloads" folder. I can only get as far as browsing for the file. But, when I click "Upload" I get a dialog that says: Upload of file failed.

At first I thought it was just my job's network but apparently it is not. I have been trying for the last 20 minutes to upload this from home. Any other way I can send it?

• Re: Count actual word in cell

Try putting it in Dropbox.com and post the link to it here.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count actual word in cell

I was busy writing a new UDF for you when I had a brainwave!

Because you need to find instances where the value entered in K2 is a whole word only, rather than a whole word or part of a whole word, then if you do the following your array formula will work.

Instead of entering "Sin" in K2 enter " Sin "

A leading and trailing space means that only whole words will be counted.

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count actual word in cell

You are right KjBox! Not to be a kill joy in the least but the only issue would be if there is a "period" or say, "a question mark/exclamation mark" following whatever word is entered in K2. But, this is the absolute closest the formula has gotten - thanks to your suggestion! Continuing with the use of the word "Sin", it appears a total of 7 times so 4 is super close. The 3 instances that the "space fix" does not catch are periods and question marks.

Skywriter noted before he vowed out that his UDF was not the issue. I took this to mean that since his code was not the problem that there is still a way to incorporated into an array formula.

Any chance I can still get the UDF you were working on? Again, super grateful for taking the time to work with me on this. I REALLY appreciate it as this workbook is a valuable study tool for me. Thank you!!

• Re: Count actual word in cell

I think you need to modify your formula by including more SUBSTITUTE functions that would replace a comma, period, exclamation mark or question mark with nothing. Something along the lines of

=SUM(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8)),",",""),".",""),"!",""),"?",""))))-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(INDIRECT("'"&H8&"'!B1:B"&I8),",",""),".",""),"!",""),"?",""),UPPER(\$K\$2),"")))))/LEN(\$K\$2)

It is after midnight here now and I am too tired to test and modify the formula to get it right. You have a go and if nothing works properly then post here again to say so and I will have a go at a new UDF when I wake up.

You may even need to include substitutions for colon and semi-colon too!

Excel built-in functions are always preferable to UDFs!

We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

• Re: Count actual word in cell

I will for sure test it KjBox! THANK YOU!! I didn't realize it was that late for you PLEASE get some rest. I'll for sure post back!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!