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 S
    Leibel S ✭✭✭✭✭✭
    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), "")

«1

Answers

  • Paul Newcome
    Paul 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)

  • Kayla
    Kayla ✭✭✭✭✭

    @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 S
    Leibel S ✭✭✭✭✭✭

    @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 Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    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 Newcome
    Paul 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?

  • Leibel S
    Leibel S ✭✭✭✭✭✭

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

  • Kayla
    Kayla ✭✭✭✭✭

    @Leibel Shuchat & @Paul Newcome - thank you, thank you, thank you!!

  • Paul Newcome
    Paul 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.

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    edited 08/25/22

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @PeggyLang

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

  • PeggyLang
    PeggyLang ✭✭✭✭✭

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @PeggyLang

    change in the formulas wherever it has ";" change it to "; " (with a space)

  • PeggyLang
    PeggyLang ✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!