I found this formula for extracting text in between parentheses. The formula goes in an empty column and it gets the text from the column before it:
=MID(B1,FIND("(",B1)+1,FIND(")",B1)-FIND("(",B1)-1)
How do I change it so that it finds the text in the LAST set of parentheses. Some, but not all, of the fields have multiple sets of parentheses.
TIA!!!
Hi Kioken,
Very confusing actually also it is working on my system perfectly okay.
What I have understood, as per that i tried using the mentioned formula for extracting text from parentheses e.g. (XXXXXX) so expected result should be XXXXXX
And your formula worked superbly fine. Could you please share some of the text you are trying to extract??
Thanks
Nisha Dhawan
If you like my answer please click on * Add Reputation
"If you can dream it, You can do it"
For example:
BERGOGNONE (AMBROGIO DA FOSSANO) (Italian)
I just need to get "Italian" into a new column. The formula I found produces "AMBROGIO DA FOSSANO" instead.
Try this
=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",50)),50)),")","")
Row\Col BERGOGNONE (AMBROGIO DA FOSSANO) (Italian) Italian
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
=trim(left(right(substitute(substitute("()"&a1,"(",rept(" ",len(a1))),")",rept(" ",len(a1))),len(a1)*2),len(a1)))
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks! That sort of works!Originally Posted by AlKey
Sometimes there is text after the parentheses, though:
TINTORETTO, JACOPO (JACOPO ROBUSTI) (Italian), workshop
And it returns "Italian, workshop" instead of just "Italian".
That will fail if there's text after the last set of ( ).
A1 = (1) (2) (3) (4) (5) xxx
Formula returns 5 xxx
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I think that did it! Thank you so much!!! =)
you are welcome and thanks for your feedback
Oh, you saved the day. It's a thing of beauty. =)
It would've been more helpful to know beforehand that there is text after the parentheses.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,")","("),"(",REPT(" ",50)),50*3,50))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks