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

Leibel S

@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

@Leibel Shuchat

THANK YOU!!!!!!

Paul Newcome

@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

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

@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

@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

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

@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

@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

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