Converting a names column to email addresses (with a twist) (part 2)

S Connelly
S Connelly used Ask the Experts™
I have another Excel puzzle that I am trying to solve.

I am trying to convert a name column into an email list. It sound simple enough, but there is a small twist, that is, some names included preferred first names in brackets, and some names have three names. :P

Note: The names the example spreadsheet are a good representation of the list I will use IRL:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English - the first name in brackets will be used with the last name.
- Some names have three components.

Column A               ->  Desired name conversion

Bobby Robinson            ->      - solution found
Lin Hon (Adam) Xiao      ->               - solution found
Lang Lin Lee  ->                                  - need a fix.

So the last scenario still needs to be resolved.

The current formula (thanks to Ryan Chong), doesn't take into account a name with three parts.
For example, a name like "Lang Lin Li", converts to but should be

I was thinking that I could parse the new solved names column with a =SUBSTITUTE(G9,".","",1), but it would also have to take into account to only remove the first instance of a ".", but only when there is more than 1 "."

Is it possible to add additional complexity to the existing formula?

Current formula:
=LOWER( IF( ISERROR( MID(A5,SEARCH("(",A5)+1,SEARCH(")",A5)-SEARCH("(",A5)-1)), SUBSTITUTE(A5," ",".") & "", MID(A5,SEARCH("(",A5)+1,SEARCH(")",A5)-SEARCH("(",A5)-1) & "." & TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",100)),100)) & "" ) )

If not, calculating from a secondary row is also acceptable.

Spreadsheet that you may work with:

Can anyone with better skills than me, help me with a solution?

Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
as mentioned,  you could try this formula:

=LOWER( IF( ISERROR( MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1)),  IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>1, SUBSTITUTE(SUBSTITUTE(A2," ","."),".","",1), SUBSTITUTE(A2," ",".")) & "", MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1) & "." & TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) & "" ) )

Open in new window

S ConnellyTechnical Writer


Two absolutely brilliant formulas in less than 1 hour.

Thank you again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial