Forum Discussion

nmnick89's avatar
nmnick89
Copper Contributor
Aug 08, 2023

Potentially complex formula

Hello. This is my first post on here so bear with me.   I have a workbook with full name cells that have first and last name data that are formatted as follows: "Mickey De La Ratones (nickname)" wi...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Aug 08, 2023

nmnick89 

Are names like

Alexandre Manuel de la Vega Martíne (nickname)

i.e. last name is between "de la" and "(" ?

  • nmnick89's avatar
    nmnick89
    Copper Contributor
    Aug 09, 2023
    SergeiBaklan, in theory, I would want the formula to capture "Manuel de la Vega Martíne" without the quotes. It seems in your example that Manuel is a second first name though (correct me if I'm wrong). I can deal with this because I have so few if any records of people with multiple first names so I can manually adjust them if needed. It's just last names with multiple words that tripping me up right now.
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Aug 09, 2023

      nmnick89 

      If not 365, same as SnowMan55 sample gives

      =TRIM( MID( A1,
    FIND(" ", A1),
    IFERROR(FIND("(", A1), LEN(A1)) - FIND(" ", A1)
 ) )
      • nmnick89's avatar
        nmnick89
        Copper Contributor
        Aug 14, 2023
        Thank you! I'll try this.

Resources