Cleaning up email addresses

[rdeshpande]

Hello everybody,

I have a spreadsheet with manually typed email addresses and email addresses copied from Outlook. I have encountered these situations below, but I am sure there are more. I want to either clean the email as much as possible or create a flag for someone to review the email when Alteryx cannot definitely decide the outcome, e.g., if an email comes in as john doe@xyz.com,  I want this email to be flagged..

Situation	Email before cleanup	Expected Out put
Name1 <email1>;Name2 <email2>;	John1 Doe <john.doe1@xyz.com>;John2 Doe <john2.doe@xyz.com>	john.doe1@xyz.com;john2.doe@xyz.com
Name1 <email1>;email2	John1 Doe <john.doe1@xyz.com>;john2.doe@xyz.com	john.doe1@xyz.com;john2.doe@xyz.com
Space in the email address	John1 Doe@xyz.com; John2.doe @XYZ.com	Email review Required
Space in the email address	John Doe@xyz.com	Email review Required
Space in the email address	John.doe@xyz. com	John.Doe@xyz.com
Space in the email address	John.doe@xyz .com	Email review Required

I am new to Regex and have made an attempt to use it for cleaning the email addresses. However, I have not been very successful 🙄. Please see my attached attempt for your reference.
 
Any help is appreciated. Feel free to throw away my workflow and suggest something completely different.
 
Note: I kept each formula tool separate to see the result of each formula.

[shancmiralles]

I CONNECTED A TEXT TO COLUMN TOOL () WITH THE FOLLOWING CONFIGURATION:

COLUMN SPLIT: Email before cleanup

DELIMETERS: ; 

SPLIT TO ROWS (not Split to columns)

I CONNECTED ANOTHER  TEXT TO COLUMN TOOL () WITH THE FOLLOWING CONFIGURATION:

COLUMN SPLIT: Email before cleanup

DELIMETERS:<>

SPLIT TO ROWS (not Split to columns)

I CONNECTED A FILTER TOOL () WITH THE FOLLOWING CONFIGURATION:

(BASIC FILTER)

FIRST DROP DOWN: EMAIL BEFORE CLEAN UP  (WHICH COLUMN TO FILTER)

2ND DROP DOWN: "CONTAINS" (CRITERIA, WE'RE LOOKING FOR DATA WITH @ IN IT)

 3RD SPACE:  @   

IF YOU LIKE THE CUSTOM FILTER HERE'S THE FORMULA:

Contains([Email before cleanup],"@")

I CONNECTED A REGEX TOOL ()  WITH THE FOLLOWING CONFIGURATION:

COLUMN TO PARSE: Email before cleanup

Format to Convert:    ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

(doesn't matter if you check or uncheck the case sensitive option based on your data, but try and play with it with your other sample email address input)

Output Method: Match

Column name for match status:  Situation_Matched

(unclick "error if not match)

I CONNECTED A formula TOOL () WITH THE FOLLOWING CONFIGURATION:

Dropdown column:  Expected Out put

formula:  IIF([Situation_Matched], [Email before cleanup], "EMAIL REVIEW REQUIRED")

data type : v_wstring



***

hope this helps!