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

Options
2»

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @PeggyLang

    It should be: 3/4/3


    =IFERROR(MID([Description field]@row + "; ", FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) + 1, FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 4)) - FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) - 1), " ")

  • PeggyLang
    PeggyLang ✭✭✭✭✭
    Options
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @PeggyLang To pull the last one it should be 3/4/3 (3rd semicolon to start, 4th semicolon minus 3rd semicolon for the number of characters).


    =IFERROR(MID([Description field]@row + "; ", FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) + 1, FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 4)) - FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) - 1), " ")

  • Matthew Cleverly
    Options

    I ran across this and it is exactly what I was looking for to extract multiple bits of text from a longer string.

    As I applied this to the full data set that I am working with I noticed that the output would either cut short or extend beyond the point it should. This makes it seem that there is something not calculating correctly in the MID portion of the formula, but I am not sure.

    The text within the reference cell is separated with a pipe instead of parenthesis and looks something like this:

    Spare #1 | Quantity: 1 | Spare PN: 8X-05XX-0X | Spare Description: CONTXXXXXX - DIRECTXXXXX SHELF | Specific Slot: null | Defective Serial Number: null | Asset Index: null

    As you can see, the amount of text between each pipe varies. but I am not sure why that negatively affects the outcome.

    As an example when using the formula below I get the output Spare Description: CONTXXXXXX - DIRECTXXXXX S

    =IFERROR(MID([Dispatch Items Summary]@row, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, "|", "~", 3)) + 1, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, "(", "~", 3)) - 1), "")

    Would appreciate any help on this.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @Matthew Cleverly

    you need to change things slightly to match your situation.

    See below bolded areas for example

    =IFERROR(MID([Dispatch Items Summary]@row, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, " | ", "~", 3)) + 3, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, " | ", "~", 4)) - FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, " | ", "~", 3)) - 3), "")

  • Matthew Cleverly
    Options

    @Leibel S you are a genius!

    I applied these changes to the respective formulas for each of the items I need to pull and it worked like a dream.

    I hadn't taken into consideration the spaces on either side of the pipe or that there was no lead pipe in the string so second FIND needed to reference the "closing" pipe.

    Thank you so much for your help on this and your contributions to the community. 🌐

  • cabbsman
    cabbsman ✭✭✭✭✭
    edited 08/04/23
    Options

    In the formula provided above, what do I need to change to pull from the 7th field (delimited by a pipe)?

    =IFERROR(MID(Summary@row, FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 3)) + 3, FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 4)) - FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 3)) - 3), "")


    I tried the below but it pulls nothing.

    =IFERROR(MID(Summary@row, FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 6)) + 6, FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 7)) - FIND("~", SUBSTITUTE(Summary@row, " | ", "~", 6)) - 6), "")

    Here's the string I need to pull from. I'm looking for the agncab.

    Deployment | ANA | Cisco | CAR | NC | HAB | agncab

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @cabbsman It looks like you may have grabbed the wrong one. You want to update this one with your delimiters:

    =IFERROR(MID([Description field]@row + "; ", FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) + 1, FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 4)) - FIND("~", SUBSTITUTE([Description field]@row + "; ", "; ", "~", 3)) - 1), " ")


    Then you would adjust the numbers in bold. To pull the 7th entry, it would be 7, 8, 7.

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Options

    @cabbsman

    You would need the same as @Matthew Cleverly

    You need to take into account the spaces around the |

    As well in this case you are looking for the 7th value (which does not have a pipe at the end):

    See below revised formula accordingly

    =IFERROR(MID([Dispatch Items Summary]@row, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, " | ", "~", 6)) + 3, FIND("~", SUBSTITUTE([Dispatch Items Summary]@row + " | ", " | ", "~", 7)) - FIND("~", SUBSTITUTE([Dispatch Items Summary]@row, " | ", "~", 6)) - 3), "")

  • cabbsman
    cabbsman ✭✭✭✭✭
    edited 08/04/23
    Options

    That did it! Thank you.

    =IFERROR(MID(Summary@row + " | ", FIND("~", SUBSTITUTE(Summary@row + " | ", " | ", "~", 6)) + 1, FIND("~", SUBSTITUTE(Summary@row + " | ", " | ", "~", 7)) - FIND("~", SUBSTITUTE(Summary@row + " | ", " | ", "~", 6)) - 1), " ")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!