cut off words (or replace by "") in list from strings

[ooptennoort]

Power Query: I have a column with strings and a list of words I need found in that column AND (this is the difficult part) when found these words need to get cut off from the string (or replaced by "")? How? A sort of textual substraction... have been trying so long... list.accumulate etc... something like List. RemoveItems BUT not quite...

SOURCE	FIND IN SOURCE and EXTRACT/CUT OFF	RESULT
Some words are best left	best left	Some words are
Other words are said too often	are said to often	Other words
Fun words to say	words to say	Fun

 

[jdellasala]

I am a BIG Power Query advocate, but no need to complicate things that are simple!

Book1
	A	B	C
1	SOURCE	FIND IN SOURCE and EXTRACT/CUT OFF	RESULT
2	Some words are best left	best left	best left
3	Other words are said too often	are said to often	#PHRASE NOT FOUND!
4	Fun words to say	words to say	words to say
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IFERROR(RIGHT(A2,LEN(A2)-FIND(B2,A2)+1),"#PHRASE NOT FOUND!")

NOTE: The FIND function is case sensitive. Replace it with SEARCH for case insensitive result:

Book1
	A	B	C
1	SOURCE	FIND IN SOURCE and EXTRACT/CUT OFF	RESULT
2	Some words are best left	Best left	best left
Sheet1

Cell Formulas
Range	Formula
C2	C2	=IFERROR(RIGHT(A2,LEN(A2)-SEARCH(B2,A2)+1),"#PHRASE NOT FOUND!")