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]

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	100,000
buy 80,000 STG	80,000
buy 20,000 STG	20,000
buy 5000 STG	5,000
buy 4500 STG	4,500

<tbody>
</tbody>

Unlike some of your earlier examples, is the number you want to retrieve always going to be the second "word" in the text? If so...

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

or, alternately, you could use this one as well...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99))

 

[Dharmalingam]

Unlike some of your earlier examples, is the number you want to retrieve always going to be the second "word" in the text? If so...

=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

or, alternately, you could use this one as well...

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),100,99))

Brilliant, you are really awesome, all the three formuale are working !!

 

[István Hirsch]

You may want to give these formulas a try (enter them into B1):

To extract 3-letter words following 'Buy':

=MID(A1,SEARCH("Buy",A1)+4,3)

To extract the number following 'Buy':

=LOOKUP(10^12,--MID(A1,SEARCH("Buy",A1)+4,{1,2,3,4,5,6,7,8,9,10,11,12}))

I am not sure (could not check) if the latter formula works for you, because of my different regional settings for the numbers.