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
I love reading books
Sometimes wbd rocks the cradle	wbd rocks
Single
Two words	words

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="bgcolor: #FFFF00"]love reading[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(" ",A1&" ")+1,999)," ",REPT(" ",999),2),999))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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