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

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

THANK YOU!!!!!!

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

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: 8X05XX0X  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.

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

@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. 🌐

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

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

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

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
Categories
Check out the Formula Handbook template!