Forum Discussion
Remove certain string from text in the column
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"
You can add a custom column with this formula
= Text.Combine(List.Select(Text.Split([Name], " "), each not(Text.Contains(_, "PL"))), " ")
Pat
3 Replies
You can add a custom column with this formula
= Text.Combine(List.Select(Text.Split([Name], " "), each not(Text.Contains(_, "PL"))), " ")
Pat
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?
