Help with Formula

fa35

New Member
Joined
Jul 26, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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)


 

Attachments

  • Remove Brackets.png
    Remove Brackets.png
    8 KB · Views: 33

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Based on the picture please test this:

Excel Formula:
=MAP(A2:A5,LAMBDA(a,IF(LEN(a)-LEN(SUBSTITUTE(a,") (",""))=0,TEXTBEFORE(a," ("),TEXTBEFORE(a," (",2))))
 
Upvote 0
Welcome to the MrExcel board!

Another possible variation

24 07 26.xlsm
AB
1
2xxxxxxxxxx (remove)xxxxxxxxxx
3xxxxxxxxxx (remove(remove))xxxxxxxxxx
4xxxxxxxxxx (xxx) (remove)xxxxxxxxxx (xxx)
5xxxxxxxxxx (xx) (remove(remove))xxxxxxxxxx (xx)
Removexxxx
Cell Formulas
RangeFormula
B2:B5B2=BYROW(A2:A5,LAMBDA(r,TEXTBEFORE(r," (",1+ISNUMBER(FIND(") (",r)))))
Dynamic array formulas.
 
Upvote 0
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.
 

Attachments

  • Remove Brackets.png
    Remove Brackets.png
    8.5 KB · Views: 30
  • Remove Brackets.png
    Remove Brackets.png
    8.5 KB · Views: 19
Upvote 0
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.
 
Upvote 0
Not sure if it covers all the scenarios but try this:

Excel Formula:
=MAP(A1:A6,LAMBDA(a,LET(
instance,(LEN(a)-LEN(SUBSTITUTE(a," (","")))/2,
IF(instance=0,a,TEXTBEFORE(a," (",instance)))))
 
Upvote 0
Try:
Book1
AB
1xx (remove)xx
2xx (remove(remove))xx
3xx (xx) (remove)xx (xx)
4xx (xx) (remove(remove))xx (xx)
5xx (xx) xx (remove)xx (xx) xx
6xxxxxx
Sheet3
Cell Formulas
RangeFormula
B1:B6B1=MAP(A1:A6,LAMBDA(m,TEXTBEFORE(m," (",-1,,,m)))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top