Extracting Email address from columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 | |
11111 | abc@abc.com |
22222 | abc@amazon.com; aaa@amazon.com |
33333 | bbb@bbc.com, ccc@bbc.com |
Expected Outcome
Account Number | |
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@IraWatt Of course soo simple :).
I do have a similar scenario but this time an email address in imbedded in text
Account Number | 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 | |
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@IraWatt Just doing some testing and i have noticed a scenario.
Data Input
Account Number | |
33333 | 010704@mail.bot.com.pl;alteryx@mail.bot.com.uk |
Expected Out come
Account Number | |
33333 | 010704@mail.bot.com.pl |
33333 | alteryx@mail.bot.com.uk |
Current Out come
Account Number | |
33333 | 010704@mail.bot.com.pl |
33333 | ;alteryx@mail.bot.com.uk |
Is there anyway i can exclude the ";" from the "Email" Column
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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 ;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@IraWatt That issue been resolved, however another issue has appeared:( (Dam testing)
Data Input
Account Number | 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 | |
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 | |
33333 | 010704@mail.bot.com.pl |
33333 | alteryx@mail.bot.com.uk |
33333 | Amazon@mail.bot.uk/Salesforce |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@IraWatt Unfortunately i do.
Example emails like the following "Amazontrading@trading-amazon.com" do not passed in the end result
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
