Forum Discussion
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)" 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.
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).
- Thank you! I'll try this.
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.
- Mickey De La Ratones (nickname)
What is your expected result from above string?
The regular expression may accomplish it.- A2 hold Mickey De La Ratones (nickname)
B2
=WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~^\S+\s([^(]+)\s\(~" & A2 & "~1")
Are names like
Alexandre Manuel de la Vega MartÃne (nickname)
i.e. last name is between "de la" and "(" ?
- 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.
