Extract Word



How to extract any word after particular word from a paragraph in excel.

Thank You...



Selected Answer


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).



Thank you for your response...

I couldn't understand your method.
Can you make me understand with the example?

Thank You...
Nimesh (rep: 2) Feb 22, '21 at 6:23 am

Have you tried the macro (running it on a cell with a few lines of text and giving a known word in the InputBox)?

The macro just takes an input from the user and makes it lowercase (via Lcase). Likewise the cell value has all its words made lowercase (via LCase) for comparison and the surrounding Split function divides that lowercase string into individual words (with the space " " as a delimiter for that split) in an array (numbered from 0 up).  The loop checks if the rth split word matches the (lowercase) IndexWord and if so adds the next (r+1)th word to the output string NextWords.
John_Ru (rep: 792) Feb 22, '21 at 6:37 am

No, I have not tried the macro.
Your mentioned method is very difficult for me.
Do you have another easy method?
Nimesh (rep: 2) Feb 22, '21 at 7:19 am

I don't have an easier method but see Revision 1 to my Answer and the file where you can try the macro very easily.
John_Ru (rep: 792) Feb 22, '21 at 7:25 am
Add to Discussion

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. 


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. 



Interesting but I can't see your full formula sadly (it cuts off at "TRI...".). Also it seems to detect a word (including poor spelling of same) but the user was looking for the word AFTER the sought word (I believe).

Suggest you edit  your Answer to use the CODE button and paste the full formula where it says "Code_Goes_Here".

Thanks in advance.
John_Ru (rep: 792) Feb 24, '21 at 5:21 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login