Extract 2 words right and 2 words left from a sentence of a cell

[shivachary]

Hi All, can any one please help me get the below result in excel ?

Sentence :

[TABLE="width: 241"]
<tbody>[TR]
[TD="width: 241"] Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this company.[/TD]
[/TR]
</tbody>[/TABLE]


From the above sentence, if i find the word company, i need to extract 2 words left to it and 2 words right to it.

Ex: Again, this ( 2 words left)
company (the word to be searched for)
is the ( 2 words right)


Notice : the word company is repeated at the end of the sentence too, you may ignore that instance,


Thanks in advance.
Iam doing sentiment analysis, request some one to help.

 

[Peter_SSs]

Possibly as below, but what should happen if the first "company" in the cell is the first or second word, or second last or last word?

Excel Workbook
	A	B
1	Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this company.	Again, this company is the
Extract Words

 

[shivachary]

Iam doing Text-mining (sentiment analysis), based on the above formula, i will get the 2-3 words to right and left of it, so i will again use another formula (the result is now a short uncompleted sentence) to search for positive and negative words , and determine if the sentence is positive or negative, and further get a positive and negative score for it.

 

[shivachary]

Thank you somuch MASTER for the quick reply, truly sorry for the delayed response, i apologize.

I have tried the solution you gave, its GIVES ME what I NEED...! Than you..! ITs Awesome..

But there is a minor change i need in that.

Instead of manually inputting the search keyword "company" in the formula, if i give cell reference, it doesnt work, request you please help me with this ..

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",50)),SEARCH("company",SUBSTITUTE(" "&A1," ",REPT(" ",50)))-150,300)) = WORKS PERFECTLY

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",50)),SEARCH(C1,SUBSTITUTE(" "&A1," ",REPT(" ",50)))-150,300)) = ERROR, AS I GAVE CELL REFERENCE = C1.

Sorry for re-request, i should have been more clear earlier, but i was experimenting to build a sentiment analysis model based on the key word search.

Also, the key word "company" can be replaced with 2-3 words some times,or even more in some cases, ( based on the lexicons classifications for positive and negative i defined)
so i request for the formula should be able to work for this situation, if this is more complex , you may ignore.

But i please request to provide solution for the changes.


to extract key word i use this formula : ={INDEX(job_desc,MATCH(TRUE,ISNUMBER(SEARCH(job_desc,K2)),0))}
named range used : job_desc


Sorry again, thanks in advance. Please help me.

 

[Peter_SSs]

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",50)),SEARCH(C1,SUBSTITUTE(" "&A1," ",REPT(" ",50)))-150,300)

That formula works fine for me

Excel Workbook
	A	B	C
1	Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this company.	Again, this company is the	company
Extract Words

Also, the key word "company" can be replaced with 2-3 words some times,or even more in some cases, ..

Try this version. It should work for 1 and up to about 10 words.

Excel Workbook
	A	B	C
1	Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this great company.	Again, this company is the best	this company is
Extract Words (2)

 

[shivachary]

AWESOME..!!! Thanks a LOT MASTER..!

The is is incredibly perfect. Truly appreciate your very very quick response...!!

You are right Sir, I admit, its my mistake, even the first formula worked for me...!!


A Zillion upvotes to you..!

 

[Peter_SSs]

You are welcome. Glad it seems to be working so well for you.

 

[shivachary]

Master, i need one more help,

Need a formula to define Non-English Language.
Just return binary variable : 1 for English & 0- for non-English language (ex : Russian, Spanish, Greek etc any language other than English).

I want to use it in continuation for the above solution,
I mean, if i can seggregate English language comments, so that i can run the NEW Request formula(identify only english) for the above provided solution (originally posted in this thread)

Much appreciate you Help.

 

[Peter_SSs]

Sorry, I don't have a formula to identify language.

 

[shivachary]

No Problem, Thanks for your Kind response Master.. !