Help with Formula

[fa35]

I'm looking for some help with a formula - I'm trying to remove the brackets and text contained within the brackets but I have various scenarios - I've managed to get a formula which removes the brackets but it only works in the first scenario from the examples in the image below ie where there is only 1 set of brackets but there are scenarions where there are multiple sets of brackets - where there is more than 1 set, I want to keep the first set of brackets with the text.

The formula I was using is below.

I would really appreciate any help you guys can offer

=IFERROR(LEFT(AK2,FIND(CHAR(1),SUBSTITUTE(AK2,"(",CHAR(1),LEN(AK2)-LEN(SUBSTITUTE(AK2,"(",""))))-1),AK2)

 

[hagia_sofia]

Based on the picture please test this:

=MAP(A2:A5,LAMBDA(a,IF(LEN(a)-LEN(SUBSTITUTE(a,") (",""))=0,TEXTBEFORE(a," ("),TEXTBEFORE(a," (",2))))

 

[Peter_SSs]

Welcome to the MrExcel board!

Another possible variation

24 07 26.xlsm
	A	B
1
2	xxxxxxxxxx (remove)	xxxxxxxxxx
3	xxxxxxxxxx (remove(remove))	xxxxxxxxxx
4	xxxxxxxxxx (xxx) (remove)	xxxxxxxxxx (xxx)
5	xxxxxxxxxx (xx) (remove(remove))	xxxxxxxxxx (xx)
Removexxxx

Cell Formulas
Range	Formula
B2:B5	B2	=BYROW(A2:A5,LAMBDA(r,TEXTBEFORE(r," (",1+ISNUMBER(FIND(") (",r)))))
Dynamic array formulas.

 

[fa35]

This formula works a treat but I've received an updated data set which has created another few scenarios - there will be some values with no brackets and others with text after the brackets which has to be retained.

I did try to break down the formula myself to make the changes but I'm not familiar with a few of these functions.

 

[Peter_SSs]

Could you give us the sample data and expected results with XL2BB* instead of an image as we cannot copy from an image for testing?

* If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.

 

[hagia_sofia]

Not sure if it covers all the scenarios but try this:

=MAP(A1:A6,LAMBDA(a,LET(
instance,(LEN(a)-LEN(SUBSTITUTE(a," (","")))/2,
IF(instance=0,a,TEXTBEFORE(a," (",instance)))))

 

[Cubist]

Try:

Book1
	A	B
1	xx (remove)	xx
2	xx (remove(remove))	xx
3	xx (xx) (remove)	xx (xx)
4	xx (xx) (remove(remove))	xx (xx)
5	xx (xx) xx (remove)	xx (xx) xx
6	xxx	xxx
Sheet3

Cell Formulas
Range	Formula
B1:B6	B1	=MAP(A1:A6,LAMBDA(m,TEXTBEFORE(m," (",-1,,,m)))
Dynamic array formulas.