Parts of speech count

  • Looking for a way to count parts of speech in an Excel text string. I have a list of text strings in Column A and the following labels in Columns B to E: Noun, Adjective, Verb and Adverb. I want to know how many of each (Noun, Adjective, Verb, Adverb) are identified by text string. For example:



    [TABLE="width: 500"]

    [tr]


    [td]

    Text String

    [/td]


    [td]

    Noun

    [/td]


    [td]

    Adjective

    [/td]


    [td]

    Verb

    [/td]


    [td]

    Adverb

    [/td]


    [/tr]


    [tr]


    [td]

    The quick brown fox jumped over the lazy dogs.

    [/td]


    [TD="align: center"]2[/TD]
    [TD="align: center"]3[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]0[/TD]

    [/tr]


    [tr]


    [td]

    John likes fast blue cars.

    [/td]


    [TD="align: center"]2[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]1[/TD]
    [TD="align: center"]1[/TD]

    [/tr]


    [tr]


    [td]

    Etc.

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Etc.

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]



    While I understand performing such calculations is highly hinged on context and the English language contains various grammar rules, etc. I am not looking for a perfect solution but anything that can get remotely close would be tremendously appreciated.

  • Re: Parts of speech count


    1) What have you tried so far?
    2) Can you upload a sample workbook with anything you have tried so far included?


    Assuming you're yet to really try anything, consider this:


    In order to determine the part(s) of speech for each word, you will need to have a list of each word you're looking for that identifies it and associates it with the appropriate part(s).
    There is apparently no way of leveraging Word's grammar checker in VBA.
    So, you have some options to consider. The first approach that occurs to me would be to have a table of all the words you're interested in, with columns for each part. You can flag each word in the appropriate column(s) - a simple X will suffice. Then you need some pretty simple VBA to search for the word in the list.


    Have a go, and upload what you've achieved if you get stuck. I'm much more confident that you'll get help if you have a try first. :)

  • Re: Parts of speech count


    Thank you! I tried using different lookup/count formulas which did not result in much success (very likely because I am doing something wrong). Attached is a sample workbook where Sheet 1 contains the various text strings and placeholder cells for nouns, adjectives, verbs and adverbs. Sheet 2 contains the glossary of nouns, adjectives, verbs and adverbs. I don't know how to cross-reference these lists in order to count the different parts of speech by text string.

  • Re: Parts of speech count


    This took longer than I thought, and it isn't perfect, but I think it will help.


    Ii have:
    1) Formatted the table on your dictionary sheet as a Table.
    a) This means you have to insert row(s) when adding words to the longest list (in this case Nouns) to ensure all words are tested for.
    b) Avoid gaps in the lists in teh dictionary - this will probably stop looking if it encounters a blank cell. (untested)
    2) Named the new table "Dictionary"
    3) Created a UDF (User Defined Function) to do the maths
    4) Call the UDF from the worksheet cell formula


    Note: With the provided dataset, this does not work, as it will not recognise the last word in a string if it ends with a punctuation mark. I know there's a clever way to "clean" strings of certain characters but it's almost 1am and I have been on this for what feels like 3 hours now... (Edit: it's late, I'm tired...)


    Good luck.

Participate now!

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