Validate email address with Formula tool and output reports as Excel workbooks

[45179902]

I'm trying to accomplish three things. I'm not very familiar with regular expression, and I continue to run into errors. I have a data table looks something like this:

CODE	ID	First Name	Last Name	Email
E202201	20220101	Jason	Sullivan	jsullivan@gmail.com
E202201	20220102	Joseph	Raven	jrs@HOTMAIL.com
E202201	20220103	Callan	McDosky
E202201	20220104	George	Lee	z-li@school.edu

(all data presented are fictional)

1) I'm trying to validate if the email addresses are valid. I want to add a column that includes flags indicating whether the address is empty "Not Applicable", valid "Valid" or invalid ("Not Valid").

2) After that I want to output this data to an Excel workbook that should be created at output. I want the workbook's name to be "report-for-CODE.xlsx". In this example, it should be "report-for-E202201.xlsx".

3) I will connect to different data tables stored in Excel workbooks, and I want all the output files to be stored in the same directory.

How do I use a Formula tool to achieve the first two goals?

[stevemarkovick]

I’ve been trying to figure out a clean way to validate emails and output dynamic Excel reports in Alteryx, and your explanations (especially about regex and handling missing CODE values) really helped clear things up.
Also, huge thanks for sharing the screenshots and sample workflows — made it way easier to follow along.
Really appreciate how you addressed edge cases too, like blank fields and proper filename generation.