Jul 15 2022 12:19 PM
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 |
Jul 15 2022 01:00 PM
=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.
Jul 15 2022 01:03 PM
Can you provide a list of all unique titles that occur in your data?
Jul 15 2022 01:10 PM
Jul 15 2022 01:16 PM
Try @Quadruple_Pawn'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).
Jul 15 2022 01:21 PM
Jul 21 2022 06:07 AM
Jul 21 2022 08:39 AM
=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.