Removing the text content of one column from another column

[BeraG]

I am trying to remove the entire content of a text column from another column, e.g. removing " 10 episodes, 2018" from "Iyad Adi Kassar 10 episodes, 2018". The text in the column to be removed is the result of cleaning from the longer text in the previous column, which did not have clear delimiters and presented the problem of two or three names and other issues.

Now I have the two columns I wanted, and the text from the second should simply be eliminated from the first. I've tried with this line of code:

#"Added Custom Column1" = Table.AddColumn(#"Renamed Columns", "Character", each Text.Remove([Column4], [Episodes]))

This, however, gives me an error saying:

Expression.Error: The value isn't a single-character string.
Details:
Value= 10 episodes, 2018

Any ideas? Thanks!

[BeraG · Accepted solution]

Thanks for the idea, Nate. While your solution still did not fit perfectly, because the original column has a less than regular content pattern, and the two space delimiters work well with three names, but not with two or four, I ended up adapting your code to the following:

Text.BeforeDelimiter([Column4], " ", {2, RelativePosition.FromEnd})

which works perfectly. Thanks once again!

[watkinnc]

Darn, I meant to tell you to use the RelativePosition.FromEnd parameter--that's what I get for answering without a computer!

--Nate

[HotChilli]

Try Text.Replace() and use "" as the replacement text

[watkinnc]

Looks like you could do 

Table.AddColumn(PriorStepOrTableName, "NewText", each Text.BeforeDelimiter([Column4], " ", 2))

--Nate