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

shivachary

New Member
Joined
Jul 26, 2013
Messages
21
Hi All, can any one please help me get the below result in excel ?

Sentence :

Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this company.

<tbody>
</tbody>


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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
AB
1Again, 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

New Member
Joined
Jul 26, 2013
Messages
21
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

New Member
Joined
Jul 26, 2013
Messages
21
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",50)),SEARCH(C1,SUBSTITUTE(" "&A1," ",REPT(" ",50)))-150,300)
That formula works fine for me

Excel Workbook
ABC
1Again, this company is the best and pioneer on the trend. This is the reason why I will recommend this company.Again, this company is thecompany
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
ABC
1Again, 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 bestthis company is
Extract Words (2)
 

shivachary

New Member
Joined
Jul 26, 2013
Messages
21
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..!
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

shivachary

New Member
Joined
Jul 26, 2013
Messages
21
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Sorry, I don't have a formula to identify language.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,937
Messages
5,598,956
Members
414,269
Latest member
FJXMTT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top