Formula to keep specific words only based on position

[IamNewbee]

Hi guys, I am new to excel formulas and I am struggling with a formula.

I have a line of text,of which I want to keep only the 2nd and 3rd word and remove the rest. for example: cell A1 contains 'I love reading books' . I want to write a formula/vba code whichever is easier to give output in B1 as 'love reading'.

Could someone help me with this? Thanks in advance

 

[wideboydixon]

Not sure what you want to happen if there are fewer than three words. Here's a potential solution:

Book1
	A	B
1	I love reading books	love reading
2	Sometimes wbd rocks the cradle	wbd rocks
3	Single	Single
4	Two words	words
Sheet1

Cell Formulas
Range	Formula
B1	=MID($A1,IFERROR(FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),1)),0)+1,IFERROR(FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),3)),LEN($A1)+1)-IFERROR(FIND(CHAR(1),SUBSTITUTE($A1," ",CHAR(1),1)),0)-1)

WBD

 

[palaeontology]

you could try this formula in B1 ...

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

Kind regards,

Chris

 

[IamNewbee]

at the moment I am able to remove the first word using formula RIGHT(A1,LEN(A1)-FIND(" ",A1))... Struggling with the remaining words.

 

[palaeontology]

did you try both solutions provided to you ....?

 

[IamNewbee]

Thank you so much! The formula works perfectly.

 

[IamNewbee]

did you try both solutions provided to you ....?

Yes I did now and the formula works perfectly. My webpage was not refreshed when I posted that reply, so kindly ignore that.

 

[FormR]

Hi, just another option - different result for the single word though.

Excel 2013/2016
	A	B
1	I love reading books	love reading
2	Sometimes wbd rocks the cradle	wbd rocks
3	Single
4	Two words	words
Sheet2

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" ",A1&" ")+1,999)," ",REPT(" ",999),2),999))

 

[IamNewbee]

Hi, just another option - different result for the single word though.

Excel 2013/2016

	A	B
1	I love reading books	love reading
2	Sometimes wbd rocks the cradle	wbd rocks
3	Single
4	Two words	words

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" ",A1&" ")+1,999)," ",REPT(" ",999),2),999)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Even this works wonders....easier to understand. Thank you so much for your help