Forum Discussion

Vince_Lunetta_1962's avatar
Vince_Lunetta_1962
Copper Contributor
Jul 15, 2022

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
    • Vince_Lunetta_1962's avatar
      Vince_Lunetta_1962
      Copper Contributor
      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
      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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).

  • 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.

     

    • Vince_Lunetta_1962's avatar
      Vince_Lunetta_1962
      Copper Contributor
      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?

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        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.

Resources