Converting a names column to email addresses (with a twist)
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfection!
The solution provided for the problem described was nothing short of brilliant. Thank you so much, I truly appreciate your help.
The solution provided for the problem described was nothing short of brilliant. Thank you so much, I truly appreciate your help.
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?
ASKER
> 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.
If you can solve that last problem, I will really appreciate a solution.
Thank you.
ASKER
If it helps you, I am happy to create a new question so that you get extra points. :)
ASKER
Hi Ryan,
I created a part 2 for this question. :)
https://www.experts-exchange.com/questions/29169580/Converting-a-names-column-to-email-addresses-with-a-twist-part-2.html#questionAdd
I created a part 2 for this question. :)
https://www.experts-exchange.com/questions/29169580/Converting-a-names-column-to-email-addresses-with-a-twist-part-2.html#questionAdd
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" ) )ASKER
Thank you again.
ASKER
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. :)