Extracting Email address from columns

[Masond3]

Good Afternoon Team, 

Hope your well, i need some assistance 

AIM: To extract every Email address from the "Email" Column associated to a given account number and display each email address as an different row

NB : Note that in some instances there can be more than one email to a given account number, this can be separated out either  by a "," or a ";"

Scenario 

Account Number	Email
11111	abc@abc.com
22222	abc@amazon.com; aaa@amazon.com
33333	bbb@bbc.com, ccc@bbc.com

Expected Outcome 

Account Number	Email
11111	abc@abc.com
22222	abc@amazon.com
22222	aaa@amazon.com
33333	bbb@bbc.com
33333	ccc@bbc.com

looking forward to your assistance 

Regards
Masond3

[IraWatt]

Hey @Masond3,

Assuming they are always separated with a ; or , your can use the text to columns tool with those as delimiters like this:

Any questions or issues please ask :)
HTH!
Ira

[Masond3]

@IraWatt  Of course soo simple :). 
I do have a similar scenario but this time an email address in imbedded in text 

Account Number	Email	Comments
11111	abc@abc.com	Alt. fax no.: 111-222213188; 111-222250139; masond.d@abc.com
22222	abc@amazon.com; aaa@amazon.com	Office ext.5210/4409/5207/5202 other e-mail address 010704@mail.bot.com.pl, alteryx@mail.bot.com.uk

Expected out come 

Account Number	Email
11111	abc@abc.com
11111	masond.d@abc.com
22222	abc@amazon.com
22222	aaa@amazon.com
22222	010704@mail.bot.com.pl
22222	alteryx@mail.bot.com.uk

[IraWatt · Accepted solution]

@Masond3 it is really easy to forget text to column tool can also split to rows 😄 For you second one I would try Regex here's my initial go at it:

[Masond3]

@IraWatt  Just doing some testing and i have noticed a scenario. 

Data Input 

Account Number	Email
33333	010704@mail.bot.com.pl;alteryx@mail.bot.com.uk

Expected Out come 

Account Number	Email
33333	010704@mail.bot.com.pl
33333	alteryx@mail.bot.com.uk

Current Out come 

Account Number	Email
33333	010704@mail.bot.com.pl
33333	;alteryx@mail.bot.com.uk

Is there anyway i can exclude the ";" from the "Email" Column 

[IraWatt]

@Masond3 I've updated the regex:

I added ;' to the first [] that means it wont allow ,; within the first section of the email. 

Another option is to use the original setup and add a formula to remove the first character if it is a ; 

[Masond3]

@IraWatt  That issue been resolved, however another issue has appeared:(  (Dam testing) 

Data Input 

Account Number	Email	Comments
33333	010704@mail.bot.com.pl;alteryx@mail.bot.com.uk	Tel 2 No. Ext. #5210, #5207, #4409, #5202; Other E-Mail : Amazon@mail.bot.uk/Salesforce@mail.bot.com

Expected Out come 

Account Number	Email
33333	010704@mail.bot.com.pl
33333	alteryx@mail.bot.com.uk
33333	Amazon@mail.bot.uk
33333	Salesforce@mail.bot.com

Current Out come 

Account Number	Email
33333	010704@mail.bot.com.pl
33333	alteryx@mail.bot.com.uk
33333	Amazon@mail.bot.uk/Salesforce

[IraWatt]

Hey @Masond3,

I added this formula:

It will replace any punctuation which is not a . or a @ to a space which should hopefully cover everything. Do you have any email addresses with punctuation? 

[Masond3]

@IraWatt  Unfortunately i do. 

Example emails like the following "Amazontrading@trading-amazon.com" do not passed in the end result

[IraWatt · Accepted solution]

@Masond3 thats easy enough I just added '-' to the list of things not to replace with spaces here: