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
-
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), "")
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)
-
@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)"
-
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/find-max-and-min-values-in-one-cell
-
Is it possible that there would be additional text after that third set?
-
@Paul Newcome , yes.
-
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?
-
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.
-
@Leibel Shuchat @Paul Newcome I have the EXACT same question, however, am struggling to get syntax correct.
My cell contains the following;
Front Office; 106.03; Cabinets; CAP
I need to input those 4 pieces of information into 4 separate cells -
Front Office
106.03
Cabinets
CAP
Can you set me in the right direction?
-
The below should work (replace the column names as needed).
Please note, if you are trying to pull in a number you would need to use the VALUE function
IFERROR(LEFT([Input Values]@row + ";", FIND(";", [Input Values]@row + ";") - 1),"")
IFERROR(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) - 1),"")
IFERROR(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - 1),"")
IFERROR(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 4)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 3)) - 1),"")
-
@Leibel Shuchat that is ONE complicated formula!!!
I can't begin to understand it. I would LOVE to learn how to even begin building something like that.
I have the first one working.
The second one returns a blank. Have not gone beyond the second at this point.
IFERROR(MID([Input Values]@row + ";", FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) + 1, FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 2)) - FIND("~", SUBSTITUTE([Input Values]@row + ";", ";", "~", 1)) - 1),"")
-
change in the formulas wherever it has ";" change it to "; " (with a space)
-
@Leibel Shuchat THAT WORKED!
I got a bit fancy and changed my input cell to 4 items
Front Office
103.03
Cabinets
CAP
i replicated the pattern for the final formula and am now returning a blank .
Not sure what is wrong with that formula.
=IFERROR(MID([Description field]@row + "; ", FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 4)) + 1, FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 5)) - FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 4)) - 1), " ")
Help Article Resources
Categories
Check out the Formula Handbook template!
