Essentially:
I have a cell value that looks like this:
Hello - (Whats Up (HowAreYou))
I need to extract the value of what is inside the outside parenthesis:
Whats Up (HowAreYou)
How can this formula be modified to accommodate this requirement:
=MID(C11,SEARCH("(",C11)+1,SEARCH(")",C11)-SEARCH("(",C11)-1)
Try:
=MID(A1,SEARCH("(",A1)+1,SEARCH("@",SUBSTITUTE(A1,")","@",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))))-SEARCH("(",A1)-1)
use FILTERXML:
=FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A1,"(","<b>",1),")","</b>",LEN(A1)-LEN(SUBSTITUTE(A1,")","")))&"</a>","//b")
Or:
=MID(REPLACE(A1,FIND("@",SUBSTITUTE(A1,")","@",LEN(A1)-LEN(SUBSTITUTE(A1,")","")))),999,""),FIND("(",A1)+1,999)
Amongst the other good answers, you could alternatively try:
=REPLACE(LEFT(A1,MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)=")"))-1),1,FIND("(",A1),)
Or, without Excel 365:
=REPLACE(LEFT(A1,MATCH(2,1/(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)=")"))-1),1,FIND("(",A1),)
Note: This last formula requires you to enter as array formula through Ctrl+Shift+Enter
You can also use "Text To Column" function. You can find it in data tab in data tools group.
Select the cell or column that contains the text you want to split. Select Data > Text to Columns. In the Convert Text to Columns Wizard, select Delimited > Next. Select the Delimiters "space" and "other" << put "-" in the text box next to "other" > Next. Slect the location where you want that data and click finish.
Note: by this way you will end up with double bracket at the end that you can fix with replace function. Selecd your data and click find and replace. Put )) in find box and ) in replace window, click replace all.
Formula:
Assuming your data is in cell A1 Hello - (Whats Up (HowAreYou))
= RIGHT(SUBSTITUTE(A1, "))", ")", 1), LEN(SUBSTITUTE(A1, "))", ")", 1)) - SEARCH(" - ", SUBSTITUTE(A1, "))", ")", 1))