Extract the next word

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34
Hi All,

I'm having data like below in my sheet, i would like to extract the word which is next to "Buy", could any one pls help me here.

Thanks for your valuable input.
Buy EUR Sell STG</SPAN>
EUR</SPAN>
Buy USD Sell STG</SPAN>
USD</SPAN>
Sell EUR Buy USD</SPAN>
USD</SPAN>
Buy CAD</SPAN>
CAD</SPAN>

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Hi All,

I'm having data like below in my sheet, i would like to extract the word which is next to "Buy", could any one pls help me here.

Thanks for your valuable input.
Buy EUR Sell STGEUR
Buy USD Sell STGUSD
Sell EUR Buy USDUSD
Buy CADCAD

<tbody>
</tbody>

If all of the words you want to extract are three characters long like in all of your example, then give this formula a try...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"Buy",REPT(" ",99)),99)),3)
 

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34
Thank you very much for that!! its working perfectly!! you are genius!

For my understanding could you pls explain in detail about the formula.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Thank you very much for that!! its working perfectly!! you are genius!

For my understanding could you pls explain in detail about the formula.

Here is the formula...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"Buy",REPT(" ",99)),99)),3)

The red part replaces the work "Buy" with 99 spaces. The green part retrieves the rightmost 99 characters (as long a the text following the word "Buy" is less than 96 characters long, all the text following the word "Buy" will be retrieved). The blue part removes all multiple spaces plus it removes the leading (and trailing) spaces as well so that the word following the word "Buy" is now located at the left side of the processed text. The purple part grabs the leftmost three characters (which is what you wanted).
 

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34

ADVERTISEMENT

Shalll we use the same formula in case the length of the extracted word varies , for e.g

Is it possible to extract like below with same formula
Buy 100,000 STG</SPAN>100,000</SPAN>
buy 80,000 STG</SPAN>80,000</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Shalll we use the same formula in case the length of the extracted word varies , for e.g

Is it possible to extract like below with same formula
Buy 100,000 STG100,000
buy 80,000 STG80,000

<tbody>
</tbody>
No, because the length of what you want to retrieve is not a consistent number of characters long, we need a modified version of my formula. I'm thinking there is probably a shorter formula available, but this is what I came up with...

=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"Buy",REPT(" ",99)),99))&" ",FIND(" ",TRIM(RIGHT(SUBSTITUTE(A1,"Buy",REPT(" ",99)),99))&" ")))
 

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34

ADVERTISEMENT

Seems this formula returns the left most value of the cell. this is the value i got from the above formula.
buy 100,000 STG</SPAN>
buy</SPAN>
buy 80,000 STG</SPAN>
buy</SPAN>
buy 20,000 STG</SPAN>
buy</SPAN>
buy 5000 STG</SPAN>
buy</SPAN>
buy 4500 STG
buy</SPAN>

<TBODY>
</TBODY>
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Seems this formula returns the left most value of the cell. this is the value i got from the above formula.
buy 100,000 STGbuy
buy 80,000 STGbuy
buy 20,000 STGbuy
buy 5000 STGbuy
STG 4500 BUYSTG

<tbody>
</tbody>
That is because the SUBSTITUTE function is case-sensitive and you change "Buy" (upper case "B") to "buy" (lower case "b"). This should work for either...

=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(UPPER(A5),"BUY",REPT(" ",99)),99))&" ",FIND(" ",TRIM(RIGHT(SUBSTITUTE(UPPER(A5),"BUY",REPT(" ",99)),99))&" ")))

That last one will return nothing because nothing follows the word "BUY". If you will want the formula to find the "word" either in front or behind the word "Buy", that will take a much longer and uglier formula I would imagine.
 

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34
Instead of picking the number after the word Buy i tried to pick the values in between the spaces like below, but it does not work for me, i have used the formula
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1))-FIND(" ",A1)-1), could you pls help me here! any help will be appreciated. Thanks

buy 100,000 STG</SPAN>100,000</SPAN>
buy 80,000 STG</SPAN>80,000</SPAN>
buy 20,000 STG</SPAN>20,000</SPAN>
buy 5000 STG</SPAN>5,000</SPAN>
buy 4500 STG</SPAN>4,500</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
 

Dharmalingam

New Member
Joined
Jan 8, 2014
Messages
34
That is because the SUBSTITUTE function is case-sensitive and you change "Buy" (upper case "B") to "buy" (lower case "b"). This should work for either...

=TRIM(LEFT(TRIM(RIGHT(SUBSTITUTE(UPPER(A5),"BUY",REPT(" ",99)),99))&" ",FIND(" ",TRIM(RIGHT(SUBSTITUTE(UPPER(A5),"BUY",REPT(" ",99)),99))&" ")))

That last one will return nothing because nothing follows the word "BUY". If you will want the formula to find the "word" either in front or behind the word "Buy", that will take a much longer and uglier formula I would imagine.

Fabulous! Rick its working thats what all i need, you are rocking!! thank you very much for that! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

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