Hello,
How to extract any word after particular word from a paragraph in excel.
Thank You...
Hello,
How to extract any word after particular word from a paragraph in excel.
Thank You...
Nimesh
Your question doesn't say what kind of input range or output you require so this short macro will take a word from the user and deliver each word following immediately after that word (in a Message Box). It's case-insensitve and only acts on the active cell (but will cope with several occurrences of the word in the cell):
Sub GetNextWord()
Dim Words As Variant
Dim IndexWord As String, NextWords As String
IndexWord = InputBox("Which word are you looking for?", "Find word(s) following a searched word", "index")
If IndexWord = "" Then Exit Sub
IndexWord = LCase(IndexWord) ' make it lowercase
Words = Split(LCase(ActiveCell.Value), " ") 'divide cell text into words
For r = 0 To UBound(Words) ' check if each lowercase word matches
If Words(r) = IndexWord Then NextWords = NextWords & Words(r + 1) & "; " 'if so, append to list
Next r
MsgBox "Word(s) after " & IndexWord & " are: " & Chr(13) & NextWords
End Sub
You can modify it to address a set range of cells or user-defined range; assign it to a button etc.
Hope this is what you wanted.
Revision 1: Added file in response to user request to clarify. (To use, click on one of the yellow cells, click button "Get subsequent words ..." and enter a word from the selected cell (e..g "sample" for A2 or "the" for A3 then OK).
Here's my favorite piece of excel code.
Good for vocabulary or spelling quizzes.
1 point for survey
0.8 for survay
0.5 servay
The ESL student knew the correct word but had difficulty spelling it.
=IFS(SEARCH("survey",F2),"1",SEARCH("survay",F2),".8",SEARCH("servay",F2),".5",TRUE,"0")
The students entered their answers using Google forms. This works in Excel too. I emailed them the link to the form. They answered using their cellphones. I turned off the form after everyone present said they were finished. Google forms exported everything to a spreadsheet.
Then I used the cell code to evaluate their answers. The code is looking for one word in each answer. If the answer contains "survey" then a point is given. The search is case-insensitive. The student could have written "The answer is survey." and the code will still find the answer correct as long as the answer contains the word "survey". If the student misspelled the word, I expanded the code to give partial points.