Forum Discussion
Vince_Lunetta_1962
Jul 15, 2022Copper Contributor
EXTRACT text String
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 |
Can you provide a list of all unique titles that occur in your data?
- Vince_Lunetta_1962Copper ContributorHi 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
SRTry 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).
- OliverScheurichGold Contributor
=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.
- Vince_Lunetta_1962Copper ContributorThanks 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?- OliverScheurichGold Contributor
=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.
- Vince_Lunetta_1962Copper ContributorThanks. I'll give it a go Quadruple Pawn. How did you get that UserName?