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.


    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