remove text

[VinceLuna]

I have records in which some have titles.  Mrs. Sara Jones, Dr. Robert Smith.  

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

Is it possible to use your work  and put the all but B into the "PL" marker? 

@mahony pat posted

https://community.powerbi.com/t5/user/viewprofilepage/user-id/226208

My goal is to remove the "titles", keep in new column and shift the new text to the left. 

Thanks.

Vince

[serpiva64]

Hi,

it is not clear to me what you want

[VinceLuna]

@serpiva64  you are correct.  I wasn't clear. I have a field Name mixed with titles, firstnames, lastnames, and sometimes extra text after names. There is not consisentency.  

Name

Dr. Sam Smith

Susan White

Bro Elden

Bro Josh White

Sr. Sophia Anna Marie, S.J.

I transformed the table as such: 

= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}, {"Name.3", type text}, {"Name.4", type text}, {"Name.5", type text}, {"Name.6", type text}})

I want to create the table below and don't now how to compare each record for title and then move the text to the appropriate columns. 

Title	First Name	Last Name
Dr.	Sam	Smith
	Susan	White
Bro		Elden
Bro	Josh	White
Sr.	Sophia Anna	Marie

I want to strip Titles out and move them into a field (Title).  I want to find first names and last names, and move them into First Name and Last Name. Some first names are seperated by a space. 

I cleaned up the data base to find these thinking this could be some kind of comparision list. If it find Name.1 then do something (Iike move it to Title and move the next, Name.2 into an empty Name.1, Move Name 3. into Name.2 )

Thanks for the help. 

Name.1	Count	Name Title
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