Extracting specific string phrase from cell text

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
57
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 2, 2013
Messages
57
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
If you dont know your own rules the machine will struggle :)
 

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
57

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
If the find doesnt find the string you get a value error.
 

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
57
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,031
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top