Extracting Multiple Criteria Text

[Nikkii]

Hello Guys, I have already searched the entire forum but I can't find similar problem, please help.

This string is exported from lotus notes,

122,0,PDDM,"","55BZOR | FDHJ PIP-OBTE-17532, PIP-OBTE-17540_PIPING ISOMETRIC DRAWINGS","","07/31/2017 10:39 AM",96K,5,0

I want to extract the text that starts with PIP-OBTE and the dates:
Please note: The format of the PIP-OBTE string is composed of 14 characters.

Sample Output:
[TABLE="width: 500"]
<tbody>[TR]
[TD]B1
[/TD]
[TD]C1
[/TD]
[/TR]
[TR]
[TD]PIP-OBTE-17532, PIP-OBTE-17540
[/TD]
[TD]07/31/2017
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

The format of the PIP-OBTE string is composed of 14 characters.

Is there always 2 of them in a row, separated by comma/space ?
=MID(A1,FIND("PIP-OBTE",A1),30)

Is the DATE always 10 characters, mm/dd/yyyy ?
And no other occurrence of / before the date?
=MID(A1,FIND("/",A1)-2,10)

 

[Nikkii]

Jon, thank you for the prompt response, see my reply below.

Is there always 2 of them in a row, separated by comma/space ?

The subject of the email is not consistent, so they can be different. They can have a PIP-OBTE upto 5.

Is the DATE always 10 characters, mm/dd/yyyy ?
And no other occurrence of / before the date?

Yes, the date is always on 10 character format and they can have another date but its on different format.

 

[Jonmo1]

So we're good on the date then, yes?

for the other part, is it then from PIP-OBPT to the _ ?
perhaps
=MID(A1,FIND("PIP-OBTE",A1),FIND("_",A1)-FIND("PIP-OBTE",A1)+1)

 

[Nikkii]

So we're good on the date then, yes?

for the other part, is it then from PIP-OBPT to the _ ?
perhaps
=MID(A1,FIND("PIP-OBTE",A1),FIND("_",A1)-FIND("PIP-OBTE",A1)+1)

For the date, yes we're good.

For the second part, I am not sure what you mean with

is it then from PIP-OBPT to the _ ?

but I already tried the above code it gives me #value error. The _ changes sometimes it could be a space, comma, semi-colon and etc. I need it to capture all string with PIP-OBTE-XXXXX in it.

 

[Jonmo1]

I was going for the string between the first occurance of PIP-OBPT up to the Underscore.
But if that underscore isn't consistent either, that won't work.

How about
=MID(A1,FIND("PIP-OBTE",A1),14*(LEN(A1)-LEN(SUBSTITUTE(A1,"PIP-OBTE","")))/8)

 

[Nikkii]

I was going for the string between the first occurance of PIP-OBPT up to the Underscore.
But if that underscore isn't consistent either, that won't work.

How about
=MID(A1,FIND("PIP-OBTE",A1),14*(LEN(A1)-LEN(SUBSTITUTE(A1,"PIP-OBTE","")))/8)

This code works but it will remove some characters on the second entry and so on...

122,0,PDDM,"","55BZOR | FDHJ PIP-OBTE-17532, PIP-OBTE-17540_PIPING ISOMETRIC DRAWINGS","","07/31/2017 10:39 AM",96K,5,0

Output for the above example will be like: PIP-OBTE-17532, PIP-OBTE-175XX