Count actual word in cell

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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.


    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


    Can you attach your workbook
    [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!

Register Yourself Login