Extract multiple pieces of a text string that meet a criteria  between parentheses
I want to extract both the 1234 and 4567 from this text string:
"First set of data to extract between parentheses (1234) and second set of data to extract between parentheses (4567) and that is it"
I am able to pull the 1234 with this formula:
=IFERROR(MID([TEXT_COLUMN]@row, FIND("(", [TEXT_COLUMN]@row) + 1, FIND(")", [TEXT_COLUMN]@row)  FIND("(", [TEXT_COLUMN]@row)  1), "")
But I cannot figure out how to extract the second set of numbers between parentheses. Is this possible?
Best Answer

Leibel Shuchat ✭✭✭✭
The process I referenced earlier allows you to get any number of info from your text. Simply replace the numbers in the substitute function in the below formula examples:
Formula for parentheses #1
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 1)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 1))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 1))  1), "")
Formula for parentheses #2
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 2)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 2))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 2))  1), "")
Formula for parentheses #3
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 3)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 3))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 3))  1), "")
Formula for parentheses #10
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 10)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 10))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 10))  1), "")
1
Answers
You would use a very similar formula, but you would leverage the third portion of each FIND function that indicates where to start searching, find the first ")" and add 1 to it.
FIND(")", [TEXT_COLUMN]@row) +1
Dropping that into the 3rd portion of each FIND should work for you.
FIND("(", [TEXT_COLUMN]@row, FIND(")", [TEXT_COLUMN]@row) +1)
thinkspi.com
@Paul Newcome  Thank you!! THIS WORKED!
=MID([TEXT_COLUMN]@row, FIND("(", [TEXT_COLUMN]@row) + 1, FIND(")", [TEXT_COLUMN]@row)  FIND("(", [TEXT_COLUMN]@row)  1) + " " + MID([TEXT_COLUMN]@row, FIND("(", [TEXT_COLUMN]@row, FIND(")", [TEXT_COLUMN]@row) + 1) + 1, FIND(")", [TEXT_COLUMN]@row)  FIND("(", [TEXT_COLUMN]@row)  1)
Gives me an output of: 1234 4567
If I have a 3rd set of parentheses, I am trying to add a +2, but it's not working. Suggestions?
Text Column: "First set of data to extract between parentheses (1234) and second set of data to extract between parentheses (4567) and then (8910)"
@Kayla
You can Leverage SUBSTITUTE formula
See below links to a couple of examples, if you need help applying it let me know.
Basically you can substitute the nth number of parentheses with an obscure character like '~' and then get your value from there...
https://community.smartsheet.com/discussion/84210/findmaxandminvaluesinonecell
Is it possible that there would be additional text after that third set?
thinkspi.com
@Paul Newcome , yes.
@Paul Newcome @Kayla
The process I referenced earlier allows you to get any number of info from your text. Simply replace the numbers in the substitute function in the below formula examples:
Formula for parentheses #1
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 1)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 1))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 1))  1), "")
Formula for parentheses #2
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 2)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 2))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 2))  1), "")
Formula for parentheses #3
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 3)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 3))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 3))  1), "")
Formula for parentheses #10
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 10)) + 1, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, ")", "~", 10))  FIND("~", SUBSTITUTE([TEXT_COLUMN]@row, "(", "~", 10))  1), "")
@Leibel Shuchat I saw that, but I wasn't able to quickly break it down to see exactly how it works. I was hoping @Kayla would say "no" and we could use a basic RIGHT/LEN combo. Haha.
The way you have it broken down here definitely makes it much easier to follow and adapt, so definitely a big thank you for that. I am curious though... If the only parenthesis in the string are the parenthesis surrounding the bits we want to pull, could we make this work without the SUBSTITUTE bits?
thinkspi.com
@Paul Newcome
The SUBSTITUTE function has the replacement number option  SUBSTITUTE( search_text, old_text, new_text, [ replace_num ]) an option which the FIND function does not have.
By first substituting the specific occurrence of the character we want to split by (in our example "(" ), we can then use the FIND function to find the placement of this other character (in my example I use the "~") within the text and use that placement for our MID function.
If the "~" does not work for you you can try a more obscure character
If you are really worried you can foolproof it by nesting another SUBSTITUTE function in to first remove the "~" and then add it back in. See below example (finds 10th value between parentheses).
=IFERROR(MID([TEXT_COLUMN]@row, FIND("~", SUBSTITUTE(SUBSTITUTE([TEXT_COLUMN]@row, "~", ""), "(", "~", 10)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE([TEXT_COLUMN]@row, "~", ""), ")", "~", 10))  FIND("~", SUBSTITUTE(SUBSTITUTE([TEXT_COLUMN]@row, "~", ""), "(", "~", 10))  1), "")
@Leibel Shuchat & @Paul Newcome  thank you, thank you, thank you!!
@Leibel Shuchat Ah. I understand now. We use the SUBSTITUTE to determine whether we are pulling the 1st, 2nd, etc. grouping. I like it. I am definitely going to have to remember this one.
thinkspi.com