Delete text so it says everything except for a specific word

Hi, I'm organizing a book list and I have all the book information in one cell. The text of each book says something like "Chance Place
(HarperCollins Publishers, 2001)". Is there a way that I can delete only "HarperCollins Publishers" in each cell so my example would say "Chance Place (2001)"? Like maybe deleting the text right after the "(" and right before "2001"?


Thanks for the help,
Brad

This formula will do it. Data are in column A and formula are in column B.

Please Login or Register  to view this content.

When I enter the code, it says that there is an error, and I can't do it. I put the code in B1 and the data starts in A1. Copy and pasting in the cell just shows the formula even though the text category is general.

I'm not sure how to post an example workbook, but here is an example of my workbook copy and pasted:

Attention Deficit Disorder in Adults: A Different Way of Thinking by Lynn Weiss, PhD (Taylor Trade Publishing, 2005)
Beyond Anxiety and Phobia: A Step-by-Step Guide to Lifetime Recovery [Paperback] by Edmund J Bourne, PhD (New Harbinger Publications, 2001)
Digging Out: Helping Your Loved One Manage Clutter, Hoarding, and Compulsive Acquiring by Michael Thompkins & Tamara L. Hartl (New Harbinger Publications, 2009)
Dirty Secret: A Daughter Comes Clean About Her Mother's Compulsive Hoarding by Jessie Sholl (Gallery, 2010)
Don't Call Me Nuts: Coping with the Stigma of Mental Illness by Pat Corrigan, Robert Lundin (Recovery Press, 2001)

Sorry, I should have told you. You may have to replace semi-column with comma in the formula. It depends on your system configuration.

OK great. It works. thanks a lot.