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 -> email@example.com
- solution found
Lin Hon (Adam) Xiao -> firstname.lastname@example.org - solution found
Lang Lin Lee -> email@example.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 firstname.lastname@example.org but should be email@example.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?
=LOWER( IF( ISERROR( MID(A5,SEARCH("(",A5)+1,SE
), SUBSTITUTE(A5," ",".") & "@company.com", MID(A5,SEARCH("(",A5)+1,SE
& "." & 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:
Can anyone with better skills than me, help me with a solution?