Deleting parentheticals....and the parentheses

**kuni** · 04-20-2009, 07:46 PM

First of all, my apologies for not knowing all the answers. Second, thank you for them!

Here's what I'm trying to do:

As a part of a multi-step text product name filter, I need to remove any text that shows up within parentheses...including the parentheses. The parenthetical text varies from line to line.

Every time I see "Product 1 is (xyx123) awesome", I need it to spit out "Product 1 is awesome".

________________________________________________________________________
Here's what my very busy Excel guru told me:

"It's possible to do. There are a couple of caveats. You will need to make sure that there are not multiple pairs of parenthesis. You will not be able to use the substitute function or other simple functions (that I know of) to make this work.

It will require some logic using the "search" function where you search for the "(" first, then the ")". You will then need to use the text functions "left" and "right" to give you the components outside the parenthesis (left of "(" and right of ")"). Finally, you will need to append those text strings together (use "&") being careful to manage spaces (you could end up putting 2 spaces together which would be hard to detect)."
________________________________________________________________________

I comprehend about 25% of this. The person who usually does this stuff for me is sadly not available at the moment.

My spreadsheet is set up as: column a=product name, column b-z=filters.

And example of one of the current filter steps (from column F) that used to remove "@" is: =SUBSTITUTE(E2,"@","")

I hope I've done a decent job of explaining what I need...and that someone can help!

Thanks!

Eric

**shg** · 04-20-2009, 07:50 PM

Welcome to the forum.

=left(a1, find("(", a1) - 1) & mid(a1, find(")", a1) + 1, 1024)

**DonkeyOte** · 04-20-2009, 07:51 PM

Assuming

A1: ="Product 1 is (xyx123) awesome"

and as your contact states - you have max of 1 set of parentheses in the string you could perhaps use something along the lines of:

B1: =TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-FIND("(",A1&"(")+1,""))

that should work irrespective of A1 having Parentheses or not...

**kuni** · 04-20-2009, 08:38 PM

You guys are awesome!

So, those worked, but I noticed that some of the strings had more than one set of parenthesis...any idea how that can be dealt with?

E

**martindwilson** · 04-20-2009, 08:57 PM

if you have more () say

Product 1 (xyx123) is awesome product 2 (1245) is not
you can use shg's but combine to do both this will do 2 lots
=TRIM(LEFT(LEFT(A, FIND("(", A) - 1) & MID(A1, FIND(")", A1) + 1, 1024), FIND("(",LEFT(A1, FIND("(", A1) - 1) & MID(A1, FIND(")", A1) + 1, 1024)) - 1) & MID(LEFT(A1, FIND("(", A1) - 1) & MID(A1, FIND(")", A1) + 1, 1024), FIND(")", LEFT(A1, FIND("(", A1) - 1) & MID(A1, FIND(")", A1) + 1, 1024)) + 1, 1024))
but then again this is a tad unnecessary
why not if (xxxx) has to be removed
use find/replace?
find (*)
replace with nothing !

**kuni** · 04-20-2009, 08:59 PM

I am a complete Excel novice, but I tried repeating the function twice and it seemed to take care of the double parens!

Seriously, you guys are great. Some of the other forums are so focused on making you feel like a jerk and can't spare a second to help out.

Beers and burgers at my place tonight!

E