Remove certain string from text in the column

[atpbi10]

Hey!

How can I transform this column which contains certain string that starts with certain character which is in my case "PL"

Name

Jay Chua PL231221

John Cena

Arnold Black PL212233

Jack Don

To the one below? So essentially I need to remove all occurences of the string in the values based on condition.

Name

Jay Chua

John Cena

Arnold Black

Jack Don

Here is my attempt, where I created custom function but the code does not work as intended at all:

let
    Source = Excel.Workbook(File.Contents("***"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"WrongName", "Name"}}),
    #"Invoked Custom Function" = Table.TransformColumns(#"Renamed Columns", {"Name",  each if Text.Start(Text.From(_),2)="PL" then "" else _})
in
    #"Invoked Custom Function"

[Anonymous]

If the string to be deleted is at the end of the text (or better,if the text to keep is before the PLxyz pattern ), as in your examples, you can create a custom column in this way.

You could eventually change "PL" with " PL" to trim the trailing space

[mahoneypat · Accepted solution]

You can add a custom column with this formula

= Text.Combine(List.Select(Text.Split([Name], " "), each not(Text.Contains(_, "PL"))), " ")

Pat

[atpbi10]

Hey thanks for the solution, works properly. Just wanted to make sure if this is case sensitive? Like for the table in the example if I have guy named "Max Planck", he wont have his surname removed?