EXTRACT text String

[Vince_Lunetta_1962]

Good Afternoon

How can I extract common titles from a text string in an Excel cell, copy the title to another cell, remove the title from the original text string? As an example, I have about 15 common titles in about 6K records, so I'd like to look for all common titles and remove them from Name and place them into a Title Column.  Thank you.

Name

Dr Ken Jones

Rev Tom Smith

Mark Smith

Sr. Mary Elizabeth

[#2]

@Vince_Lunetta_1962 

=IF(ISNUMBER(MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$8,A2)),0)),TRIM(SUBSTITUTE(A2,INDEX($C$2:$C$8,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$8,A2)),0)),"")),A2)

Is this what you are looking for? Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

 

[#3]

@Vince_Lunetta_1962 

Can you provide a list of all unique titles that occur in your data?

[#4]

Hi Hans,
Thank you. I read some of your work on other posts. Wow!

Title
Father

DRE
TEACHER
FINANCE MANAGER
PRINCIPAL
DCN
DR
DR.
Dr
Dr.
MSGR
Presch Director
OTHER
MR
CATECHIST
MR.
MRS
MRS.
MS
Mr
Mrs
FR
Msgr
SR

[#5]

@Vince_Lunetta_1962 

Try @OliverScheurich's suggestion - enter your list of titles in C2 and down, and adjust the range $C$2:$C$8 to your range of titles.

Remark: list the longer versions of a title above the shorter versions: DR. above DR and MRS. then MRS then MR. then MR

The formula is case-insensitive so you only need to include one of DR Dr and dr (any one).

[#6]

Thanks. I'll give it a go Quadruple Pawn. How did you get that UserName?

[#7]

Thanks QuadruplePawn.
Is there a way to make this functional in a table? I kept getting a #Spill and #Value error.
How did you populate column E with the stripped Titles?

[#8]

@Vince_Lunetta_1962 

=IFERROR(INDEX($C$2:$C$8,MATCH(TRUE,ISNUMBER(SEARCH($C$2:$C$8,A2)),0)),"")

This is the formula i entered in cell E2 (the screenshot is in an earlier post). I had to enter the formula with ctrl+shift+enter because this wasn't done in Office365 or 2021.

I tried this with a dynamic table and it returned the expected results.