Extracting specific string phrase from cell text

[detweiler]

Looking to get help on how to go about extracting a specific phrase from a clump of text. I'm looking at a little over 66K rows that has a details column that is 2 to 10 lines deep in the formula bar and am needing pull single sentence out of it. I've tried using MID and Right with Search but don't know how to get the formula to find the phrase that starts with Field was changed from, select that and all of the text through to the next period so the entire sentence can be displayed in the cell to the right.

I'm sure it's probably something very simple that I didn't search for correctly and would appreciate any help.

 

[steve the fish]

There will be a few options. Heres one:

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("Field was changed from",A1),LEN(A1)),".",REPT(" ",LEN(A1))),LEN(A1)))

 

[detweiler]

steve the fish - thanks, it worked on about half of the rows.

Unfortunately I guess I broke the first rule of parsing text out of string and failed to account for variations on the theme. Also ran into a minor snag that there are occurrences to the left of middle of the string that aren't pulling in.

 

[steve the fish]

If you dont know your own rules the machine will struggle

 

[detweiler]

What's got me most confused is the non-hits on the phrase that's in the string and returning a #VALUE! error.

 

[steve the fish]

If the find doesnt find the string you get a value error.

 

[detweiler]

If the find doesnt find the string you get a value error.

Again, this is on me much to your previous admonition about not knowing my own rules or data. Seems as though there's a character limitation that contributed to the return of the #VALUE error in those rows.

Taking the provided formula, I've been able to adjust it to find the starting and ending character count location within the string for Field Owned By Team was changed from the value Team A to the value Team B. ( through the period ) which, for example, may be 545 ( FIND("Field Owned By Team",$B2) ) & 650 ( FIND(".",$B2,$C2+1) ). What I would like to do would be to replicate this functionality, of sorts, to find and put the text for Team A in E2 and the text for Team B in F2.

I guess what I'm asking is, is there a way to find the first occurrence of the word 'value' after 'Field Owned By Team' and copy the string of characters through the first occurrence of the work 'to' into E2, then find the second occurrence of the work value and copy the that string of characters through to the first '.' into F2? Thereby cutting out the middle of finding the phrase altogether because I was gong to need to get here in the end.

Thanks.