excel formula need

[VBABEGINER]

Hi,
Good afternoon,

im trying to remove bracket data "(x2)". if a1 contains data in between "()" then the bracket data should be clear and rest data remain in b1.

original
3.5 x 240 mm2 (x2)

after excel formula
3.5 x 240 mm2

note: bracket comes anywhere in data. not fix that will come in last only.

i tried from last half hour but cant. can any one pls suggest...

 

[Domski]

Try:

=IF(AND(ISNUMBER(SEARCH("(",A1)),ISNUMBER(SEARCH(")",A1))),TRIM(SUBSTITUTE(A1,MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1),"")),A1)

Dom

 

[Robert Mika]

Excel 2010
	A	B
1	3.5 x 240 mm2 (x2)	3.5 x 240 mm2
2	3.5 x 240 (x2)mm2	3.5 x 240 mm2
3	3.5 x (234)240 mm2	3.5 x 240 mm2
4	3.5 x 240 (agx234)mm2	3.5 x 240 mm2
5	3.5 x g0 mm2 (x2)	3.5 x g0 mm2
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1),"")

 

[Rick Rothstein]

Assuming there will only be one bracketed piece of text, give this formula a try...

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

If it is possible that there could be no bracketed text, then use this formula instead...

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

 

[Aladin Akyurek]

Hi,
Good afternoon,

im trying to remove bracket data "(x2)". if a1 contains data in between "()" then the bracket data should be clear and rest data remain in b1.

original
3.5 x 240 mm2 (x2)

after excel formula
3.5 x 240 mm2

note: bracket comes anywhere in data. not fix that will come in last only.

i tried from last half hour but cant. can any one pls suggest...

Try:

=IF(AND(ISNUMBER(SEARCH("(",A1)),ISNUMBER(SEARCH(")",A1))),TRIM(SUBSTITUTE(A1,MID(A1,SEARCH("(",A1),SEARCH(")",A1)-SEARCH("(",A1)+1),"")),A1)

Dom

Dom: I seem to have a shorter one...

=TRIM(LEFT(A1,FIND("(",A1)-1)&" "&REPLACE(A1,1,FIND(")",A1),""))

 

[Domski]

@Aladin

I'm not surprised. Thought it was a bit of a monster as I was typing it (although yours will error if the () do not exist I think).

Dom

 

[Rick Rothstein]

Dom: I seem to have a shorter one...

=TRIM(LEFT(A1,FIND("(",A1)-1)&" "&REPLACE(A1,1,FIND(")",A1),""))

A touch shorter yet...

=TRIM(LEFT(A1,FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,999))

And for the case where no bracketed might occur...

=TRIM(LEFT(A1,FIND("(",A1&"(")-1)&MID(A1,FIND(")",A1&")")+1,999))

 

[Robert Mika]

Afterthought:
Forgot about the TRIM
=TRIM(SUBSTITUTE(A1,MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1),""))
But I'm sure Aladin's and Rick's formulas are the better option.

 

[VBABEGINER]

wow...wowww....
Thanks God,
So many replies from great experts...
Thanks all for discussing on my small part.
This is Honorable for mi.