Removing multiple names from a text in a column

[sarathD4s]

I have a column named exception reason which is in text fomat,  within this text there will names of people which will be starting by Mr, Mrs, Miss or Ms, there could be multiple names in a single text,for example Mr A & Mr B or Miss C Mr D.

What i need is to remove the names from the data , so that no personal details are available in the text.

i tried the below  logic , but it didnt remove any of the names. is there any way to resolve this.

let
// Original text from the column
OriginalText = [Exception Reason],

// List of prefixes to check
Prefixes = {"Miss ", "Mrs ", "Mr ", "Ms "},

// Check if the text starts with any of the prefixes
StartsWithPrefix = List.First(List.Select(Prefixes, each Text.StartsWith(OriginalText, _)), ""),

// Remove the prefix if it exists
RemovePrefix = if StartsWithPrefix <> "" then
let
// Remove the prefix from the text
TextWithoutPrefix = Text.RemoveRange(OriginalText, 0, Text.Length(StartsWithPrefix))
in
Text.Trim(TextWithoutPrefix)
else
OriginalText
in
RemovePrefix

[dufoq3]

Hi @sarathD4s, this replaces first word between two spasec after defined prefixes.

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcyxCsMwDATQXzk85ydKoV2aqaPJIBKVCBzblWVI/j52m02n45737lYtbWQyUwgHCht4nzmbpIhRC/IqQTIoLi2CAu/tZP0/Cu5kK6tERmfKAFZNik+qcXHT4N07ba3j3X7p0brGjKQmEa/ax5d9WaFbT6UDyt/Kpe2mEw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Prefixes = List.Buffer({"Miss", "Mrs", "Mr", "Ms"}),
    StepBack = Source,
    Ad_Replaced = Table.AddColumn(StepBack, "Replaced", each
        [ a = List.Distinct(List.RemoveItems(List.Combine(List.Transform(Prefixes, (x)=> Text.PositionOf([Column1], x, Occurrence.All))), {-1})), //Prefixes positions
          b = List.Transform(a, (x)=> Text.BetweenDelimiters(Text.Range([Column1], x), " ", " ")),
          c = List.Accumulate(b, [Column1], (state, current)=> Text.Replace(state, current, "*"))
        ][c], type text)
in
    Ad_Replaced

[sarathD4s]

This seem sto be ot working , as it create a table and when opened its just repeats the three values that is given in the screen shot, my spreadsheet will have multiple columns and Exception reason is just one of the column and i want to see the anmes removed in  new column next to it

[sarathD4s]

Example

[Ahmedx]

you have several names in one line, post an example of several lines to help you

[sarathD4s]

i have created some sample examples on how it will be seen in the below screenshot for your reference

[sarathD4s]

Thank you so much for your solution.

I tried the three solutions but i am getting the below errors for the reasons where the names are not available. but it works for the ones with the salutation, but if there are multiplenames it wont be replaces. could you able to help with that

[sarathD4s]

This is an example of what i am trying to achive, i need to find the names and replace it with a " * " so that names are removed, names will always be starting by Mrs, Miss, mrs, Ms which can help to find the names in the text

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Ahmedx]

Can you please share your demo input and expected output!