Remove Text in a specific Column

[jhoyabel19]

Hi,
Need help, how to remove the Ms, Mr, Ms. Miss in some of the names of this column in Power Query? Thank you

[Mikelytics]

Hi @jhoyabel19 

I also built a more lean solution. with an Added Column.

if Text.StartsWith([Column1],"Mr ") or Text.StartsWith([Column1],"Mrs ") or Text.StartsWith([Column1],"Ms ") then Text.AfterDelimiter([Column1], " ") else [Column1]

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[VinceLuna]

Mike,

Thanks for this solution.  Is there a way to add a search in a table with common prefixs? I'd rather automate the replacements based upon the everchange master data set.   I have a dataset that includes DR, dr DRS. Mrs, Miss, Ms, MS, Deacon, Dcn and other combinations. I can compile a list form the data using splitter to find the most prevelant prefixes. The list looks like: 

Name.1              Count   Title Made

Sister	12	Sr.
Sr	137	Sr.
Sr.	32	Sr.
Ms	35	Ms.
Ms.	23	Ms.
Mrs	141	Mrs.
Mrs.	97	Mrs.
Mr	15	Mr.
Mr.	12	Mr.
Miss	12	Miss
Rev	8	Father
Dr	12	Dr.
Dr.	15	Dr.
Deacon	3	Deacon
	5	B
Alexandra	5	B
Alexandria	3	B

[Ashish_Mathur]

Hi,

Could you share some data and show the expected result.

[Mikelytics · Accepted solution]

Hi @jhoyabel19 

Please fo the following

Please also make sure to use a space behind Ms or Mrs.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[jhoyabel19]

Thank you so much for the help.

[Mikelytics]

@jhoyabel19  Awesome! Thank you for your feedback! 🙂