Potentially complex formula

[nmnick89]

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)" without the quotes. I'm trying to create a last name cell with a formula that parses out all the words of a last name from one of these full name cells (regardless of how many words are in the last name) and skips over the first name. I think there is something that can be done with using an open parentheses as the delimiter for when to stop, I just don't know how to do this. Anyone have some ideas on formula(s) that can accomplish this?

Thank you all in advance.

[#2]

@nmnick89 

You could try Flash Fill. But you have to provide several examples to get the AI to recognize the pattern.

But you will never get a 100% solution.

[#3]

Mickey De La Ratones (nickname)
What is your expected result from above string?
The regular expression may accomplish it.

[#4]

@nmnick89 

Are names like

Alexandre Manuel de la Vega Martíne (nickname)

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

[#5]

@peiyezhu "De La Ratones" without the quotes is the result I am trying to get.

[#6]

@Sergei Baklan, 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.

[#7]

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

[#8]

@nmnick89 

If not 365, same as @SnowMan55 sample gives

=TRIM( MID( A1,
    FIND(" ", A1),
    IFERROR(FIND("(", A1), LEN(A1)) - FIND(" ", A1)
 ) )

[#9]

Thank you! I'll try this.

[#10]

Thank you! I'll try this.

[#11]

@nmnick89 

Thanks, please share your feedback if any of answers works with you.

[#12]

A2 hold Mickey De La Ratones (nickname)
B2
=WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~^\S+\s([^(]+)\s\(~" & A2 & "~1")