how to extract text between parentheses

Hi Smarthseet community,

Can you help to figure out how to extract text between two parentheses?

For example, ABCD(12345), I would like to extract 12345 in a different cell.

Here is my formula: =MID(Item@row, FIND("(", Item@row + 1, FIND(")", Item@row) - FIND("(", Item@row) - 1)))

It gave me "UNPARSEABLE" error message.

Any suggestions are well appreciated!!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Try:

    =MID(Item@row, FIND("(", Item@row)+ 1, FIND(")", Item@row) - (FIND("(", Item@row) - 1))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    Try:

    =MID(Item@row, FIND("(", Item@row)+ 1, FIND(")", Item@row) - (FIND("(", Item@row) - 1))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Thanks, Mark! It works!

    Shuiyi

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi, Glad you found a solution. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark, what about a formula to eliminate the parenthesis text and order?

    Ex: Perez (10148), Madison , I need to be "Madison Perez". Looking for a formula to do it.

  • Hello - I was able to remove the parenthesis with the following:

    =IFERROR(MID(Item@row, FIND("(", Item@row) + 13, FIND(")", Item@row) - (FIND("(", Item@row) - 0)), "No Match")

    Constance Fetter (she/her/elle)

  • I tried Mark's approach, and it left me with the final parenthesis as part of the result. I was able to fix that by moving where it subtracts from to the first FIND in the "num-chars" position of the initial MID function (IFERROR for blank rows when converting to a column formula):

    =IFERROR(MID(Item@row, FIND("(", Item@row) + 1, (FIND(")", Item@row) - 1) - FIND("(", Item@row)), "")

    Just wanted to pass this along in case anyone else got the same initial error I did, as this was a huge help for my current project!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!