We help IT Professionals succeed at work.

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

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. :P
This means that the preferred first names must be used.

Note: The names are strictly using the the examples in the spreadsheet, there are no other variations:
- Only first and last name, if a Western name.
- May include a preferred name in brackets, if the name is non-English.

Examples:
Column A               ->  Desired name conversion

Bobby Robinson            -> bobby.robinson@company.com      
Lin Hon (Adam) Xiao      -> adam.xiao@company.com

In the example spreadsheet (tab = email addresses), I was able to each scenario separately, but not as a single unified formula.
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:
try use formula such as:

=LOWER( IF( ISERROR( MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-SEARCH("(",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.xlsx
S ConnellyTechnical Writer

Author

Commented:
Fantastic and brilliant. Thank you so much.

But there was one additional condition that I failed to mention. :(

A name like this:
Lang Lin Li  using your formula, 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 "."

It would be awesome to get that scenario covered in a single formula, but using a secondary column is perfectly acceptable.

Thank you. :)
S ConnellyTechnical Writer

Author

Commented:
Perfection!

The solution provided for the problem described was nothing short of brilliant. Thank you so much, I truly appreciate your help.
Ryan ChongSoftware Team Lead

Commented:
A name like this:
Lang Lin Li  using your formula, converts to lang.lin.li@company.com  but should be langlin.li@company.com

Ok, coool. and let me know if we still need to solve scenario above?
S ConnellyTechnical Writer

Author

Commented:
> Ok, coool. and let me know if we still need to solve scenario above?

If you can solve that last problem, I will really appreciate a solution.

Thank you.
S ConnellyTechnical Writer

Author

Commented:
If it helps you, I am happy to create a new question so that you get extra points. :)
Ryan ChongSoftware Team Lead

Commented:
probably a bit more complex for the formula, but it may not cover all the scenarios

=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

S ConnellyTechnical Writer

Author

Commented:
Thank you again.