Cleaning up email addresses

Question

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.. SituationEmail before cleanupExpected Out putName1 ;Name2 ;John1 Doe ;John2 Doe john.doe1@xyz.com;john2.doe@xyz.comName1 ;email2John1 Doe ;john2.doe@xyz.comjohn.doe1@xyz.com;john2.doe@xyz.comSpace in the email addressJohn1 Doe@xyz.com; John2.doe @XYZ.comEmail review RequiredSpace in the email addressJohn Doe@xyz.comEmail review RequiredSpace in the email addressJohn.doe@xyz. comJohn.Doe@xyz.comSpace in the email addressJohn.doe@xyz .comEmail 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. email validation.yxmd

shancmiralles · Accepted Answer

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!