Formula to remove Brackets and Text in brackets

Hi All

I need a formula that will remove the Brackets and Text from within Cells in a row. My issue is that there are certain instances where the are multiple brackets to be removed. The source date is not mine so i cannot edit it. Alternatively a VBA to do the same thing may be better. There are only a maximum of 25 rows. Here are the examples of how the text appears in the sheet attached. There are also instances where there is a "*" out of the brackets. I'm not too fussed about removing that but if something could be incorporated into the formula to remove that would be great.

do you just wish to have everything between the first open bracket and the last closed bracket removed?

Perhaps
=IFERROR(TRIM(SUBSTITUTE(LEFT(A1,SEARCH("(",A1,1)-2)&MID(A1,FIND("@",SUBSTITUTE(A1,")","@",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))),1)+1,40),"*","")),A1)

Hi,

I've tried the

Formula:

=IF(A1="","",IFERROR(TRIM(SUBSTITUTE(LEFT(A1,SEARCH("(",A1,1)-2)&MID(A1,FIND("@",SUBSTITUTE(A1,")","@",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))),1)+1,40),"*","")),A1)) 

However I get the following message - "This formula uses more levels of nesting than you can use in the current file format". I should have mentioned that the workbook im using is an XLS office 97-2003 format. I do need to change it to an XLSM, but at the moment its not possible due to a VBA script that i need to do a bit of work with to adjust to the XLSM format.

Any help would be appreciated

array formula (CSE)
=IFERROR(SUBSTITUTE(REPLACE(A1,FIND("(",A1),MAX(IF(MID(A1,ROW($A$1:$A$100),1)=")",ROW(($A$1:$A$100))))-FIND("(",A1)+1,""),"*",""),A1)

Thanks for both of your solutions. Just one other thing that I forgot to mention was that In the rows where there is blank data I would like the cell to be blank, Ive tried to add another IFERROR...."", but i'm struggling to get it in the correct place (or dont know if you can have 2) and some of the names are in CAPS so want to the use the PROPER function?

Thanks

Hi, Thanks for the responses. Ive been busy an just had chance to look at this.

I already have a helper cell that's rearranging the data. Honestly the source data on such a mess with with brackets and "*" and all sorts I just want to make it look as it should. Ive extracted what ive already got to the attached as I was slightly off with my original source data ordering.

So I want the cell to display John Smith, Paul Jones - that's it. no () or *

If put the cells in the place they are in my spreadsheet with an example of my existing attempts

Thanks again in advance

John

Sir
Please give me a formula by which i will be able to substitute brackets & inside brackets data.
Please do not use VBA.

e:g- W=41CM (16.14") L=55CM (21.65") F=8CM (3.15")

formula should get above statement like this W=41CM L=55CM F=8CM


Regards
Sonjoy Paul

Sir
Please give me a formula by which i will be able to substitute brackets & inside brackets data.
Please do not use VBA.

e:g- W=41CM (16.14") L=55CM (21.65") F=8CM (3.15")

formula should get above statement like this W=41CM L=55CM F=8CM


Regards
Sonjoy Paul