Forum Discussion
Searching for verb tenses
I need to search a large Excel document for verbs in the present and past tense. I would be satisfied if I could just find all words that end in "s" or "ed," but I can't figure out how to do that. When I search on "*s," for example, I get all words that have an s in them with a letter or letters before the s, such as "finished." Is there a way to indicate in the search field that "s" or "ed" should be the last letters? Thanks for your help.
5 Replies
MHJ_52It's not altogether clear what you mean by "large Excel document." If you mean a large Excel spreadsheet, then the follow-up questions are:
- Are the words you're needing to search through contained in a column (or row, but more likely column) that contains single words only?
- Or are they contained in large cells that contain a fair amount of text, conceivably even more than one verb per cell?
If each cell contains only single words then the prior answer you already got will work for finding those ending in "s" or "ed"
If, on the other hand, each cell to be searched contains a sentence, so long as it only contains one verb per sentence (no subordinate clauses, etc.), then you might be able to search with something like
=IF(FIND("ed ",A3),"Look here",""), changing the "ed " to "s " for the other, although be aware that the latter will find words like "This" as well as verbs ending in "s".
- If your search field is Column A, you may try this for “s”:
=RIGHT(A:A,1)=“s”
For “ed”, try this:
=RIGHT(A:A,2)=“ed”
Note that the foregoing must be nested within another formula, like SUMPRODUCT, the construction of which depends upon your requirements.
