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

S Connelly
S Connelly used Ask the Experts™
on
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.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      - solution found
Lin Hon (Adam) Xiao      -> adam.xiao@company.com               - solution found
Lang Lin Lee  -> langlin.lee@company.com                                  - 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 lang.lin.li@company.com but should be langlin.li@company.com

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," ",".") & "@company.com", MID(A5,SEARCH("(",A5)+1,SEARCH(")",A5)-SEARCH("(",A5)-1) & "." & TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",100)),100)) & "@company.com" ) )

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

Spreadsheet that you may work with:
https://docs.google.com/spreadsheets/d/1Q2nN-MVD_iwJi2Se-PFWcNbMGb-EF6gQWQCruhnWW-Q/edit#gid=833560184

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

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
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," ",".")) & "@company.com", MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",A2)-1) & "." & TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)) & "@company.com" ) )

Open in new window

29169565_ver2.xlsx
S ConnellyTechnical Writer

Author

Commented:
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