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 Community Champion
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!