Extract the next word

[Dharmalingam]

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>

 

[Rick Rothstein]

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	EUR
Buy USD Sell STG	USD
Sell EUR Buy USD	USD
Buy CAD	CAD

<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]

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]

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]

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]

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	100,000
buy 80,000 STG	80,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]

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>

 

[Rick Rothstein]

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	buy
buy 80,000 STG	buy
buy 20,000 STG	buy
buy 5000 STG	buy
STG 4500 BUY	STG

<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]

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]

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!