Selected Answer
Hi FDNYMom and welcome to the Forum
To find text between parentheses, I'd suggest that you forget LEFT and LEN but instead use Excel's functions MID (get mid-string) and FIND (find text within a string).
- MID takes two arguments- the text to be checked, the start number (for the first letter you want) and number of characters needed after that. There's an optional third argument too- the earliest character you want to search from (which is the first, if omitted)
- FIND takes two arguments- the text you're looking for and the text to be checked.
In the attached file, the yellow cell A2 contains the text "A formula (from Teach Excel) works here (to your advantage)." but you can change it.
The formula in cell B2 extracts what's between the first set of parentheses using:
=MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1)
where it takes the mid-string from one after where the first left parenthesis "(" is found and the number of characters is from where the first right parenthesis ")" is found less that first left one. You'll see it produces "Teach Excel".
In C2,I've used the optional third argument for MID so it starts to look for the next left parenthesis after "Teach Excel" (what's in B2). Also, because there may not be a second set of parentheses, the formula is wrapped by an IFERROR function (see the changes in bold):
=IFERROR(MID(A2, FIND("(",A2, FIND(B2,A2))+1, FIND(")",A2)-FIND("(",A2)),"")
so it shows "to your advantage" at present but if there wasn't a second set, it would show nothing. That's because the formula ends with "") so it shows nothing when the FIND creates an error. If you changed that last bold bit to "No second set" for example, than that phrase would appear if there isn't a second set.
Hope this makes sense and helps. If so, please remember to mark this Answer as Selected (that's how the Forum should work!).