Formula to extract particular range of text from a cell

[faizzsheikh]

Someone please help me with the below scenario:


A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook
A3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)


I want to extract text from RIGHT till the word "by"


So the result must be:
B1: by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
B2: by Steven Pressfield (Apr 20, 2011) - Kindle eBook
B3: by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook

 

[CROY1985]

this works for your example:

=RIGHT(A2,LEN(A2)-FIND(" by ",A2))

Notes:
It only works because in your example the first instance of by appears as -by-.
If there is an instance of " by " in the title, it will pull everything from the first " by " onward.

 

[faizzsheikh]

Thanks for your help.. the formula worked.

I need another assistance. This time I want the entire text before 'by'.

For example:

A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook
A3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)


So the result must be:

B1: The War of Art
B2: Do the Work
B3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen


I really appreciate your help.

 

[CROY1985]

=left(A2,find(" by ",a2))

 

[faizzsheikh]

@Cory1985

Extremely sorry, I made a mistake.

The cell that contains 'by' twice is actually without dash.
Therefore:

A3: How to Get the Raise You Want in 90 Days or Less: A Step by step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)

Please pardon for begin inaccurate. Can you please help me