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
SnowMan55's avatar
SnowMan55
Bronze Contributor
Aug 09, 2023

nmnick89 If your version of Excel supports TEXTAFTER, then try this for a full name in cell A1:

=LET( everything_after_first_name, TEXTAFTER(TRIM(A1)," "),
    position_of_open_paren, IFERROR( FIND("(",everything_after_first_name), LEN(everything_after_first_name)+1 ),
    without_nickname, TRIM( LEFT(everything_after_first_name, position_of_open_paren-1) ),
    without_nickname)

You can use shorter names than I created, but using explanatory names is a good practice.  The first TRIM function is not required, but prevents problems with any leading space(s) that might appear in the data.  The IFERROR function handles the case where no open parenthesis is found (and presumably no nickname is present).

nmnick89's avatar
nmnick89
Copper Contributor
Aug 14, 2023
Thank you! I'll try this.

Resources