Extract multiple pieces of a text string that meet a criteria - between parentheses

01/06/22
Accepted

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 ShuchatLeibel Shuchat ✭✭✭✭
    Answer ✓

    @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), "")

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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)"

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    @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/find-max-and-min-values-in-one-cell

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Is it possible that there would be additional text after that third set?

    thinkspi.com

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭
    Answer ✓

    @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), "")

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    @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!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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

Sign In or Register to comment.