If you use Google spreadsheets and you have to figure out which parts of speech (noun, verb, adjective, adverb etc.) the words in a column are then there are special formulas to do so. These formulas will query online dictionaries like dictionary.com, merriam-webster.com, and oxforddictionaries.com to find out the part of speech and will show it in a cell that you specify.
So, without further ado, here are the formula that you will use to find whether your words are noun, verb, adverb, adjective, pronoun etc.
=IMPORTXML(“http://www.dictionary.com/browse/”&A1, “(//span[@class=’dbox-pg’])[1]”)
=IMPORTXML(“https://www.merriam-webster.com/dictionary/”&A1, “(//span[@class=’fl’])[1]”)
=IMPORTXML(“https://en.oxforddictionaries.com/definition/”&A1, “(//span[@class=’pos’])[1]”)
Note that the A1 part in the formulas above represent the cell where your word is located.
Using the Formula
You can see how I have used the formula from before in the spreadsheet above. There are words in column A and the formulas use dynamic cell values like A1, A2, A3 … A6.
The formula in the first cell queries dictionary.com, the second one queries Merriam Webster’s online dictionary and the third one goes through Oxford Dictionary. How the formula is set up is that it creates a URL based on the word and opens it up using the IMPORTXML function that’s available in Google Spreadsheet.
The IMPORTXML function does the hard work of loading the URL, finding the respective class name which is <span class=”dbox-pg”> for dictionary.com as an example. Since multiple span can be there using the class dbox-pg, we have limited the output to 1 or the first span with the class name dbox-pg. We went through the HTML source to figure this out.
The limitation to this formula are:
- A single word can be a noun as well as a verb. It only picks the first one displayed in the respective dictionary.
- The formula has to load up the webpage and get the values. If there are a lot of words, querying can take some time and you might also get an error as many queries to a single site can result in a temporary block by the site’s server.
Besides that, if you use it sparingly, the formula works great. Post your improvements to it in the comments and suggest other dictionaries if you think those are better.
Beware of “ vs. ” ….
I do not know why – but I guess it depends on your keyboard or Google Account settings.
In my case, it made the difference between working and not working formula.
Hi
Thanks for the formula, I am hoping you can help me with an issue in google sheets. I get a “formula parse error” message – “we cannot parse this operator in this context” is the message when I hove over the // that are highlighted in orange. Do you know what the issue might be? Thanks in advance for your help
Are these still working for you?
I’ve tried to update the span class, but I keep getting a “formula parse error”
Do you have by any chance an updated parser formula?
I am getting same error. have you found the solution?
Thanks for this! As Dennis mentioned above, the type of quotation marks (both single & double) is important.
Also, just this Merrian-Webster one worked for me:
=IMPORTXML(“https://www.merriam-webster.com/dictionary/”&A1, “(//span[@class=’fl’])[1]”)
Parse error is caused by the blog’s font. Simply re-type the ” or ‘ in the spreadsheet and it will work normally.
I did that, but the error persisted.
I am not getting your point. can you elaborate it with an example.
Please make it possible to show For multiple parts of speech class words within a cell