last, first to firstname.lastname@mycompany.com

Jennifer Seymour

Is there a way to take a column:

Last, First

and build

Firstname.lastname@mycompany.com

Scenario:

I have data that is emailed to me as a cut and paste of part of a table. I send an email using the names included in this data. I have no control over the original spreadsheet, (so cannot have the sender manipulate the data in excel before sending)

Old (quasi manual) Process:

Copy paste table from outlook message into Excel - I have a formula that flips the names into a new column (First Last), I then manually copy and paste the "First Last" into Outlook which uses the company directory to look up the people. I resolve any missing/wrong names and send a standard email to the people on the spreadsheet.

New Process:

Copy paste table from outlook message into Smartsheet - use ? to take Last, First and turn it into Firstname.lastname@mycompany.com. In a totally perfect world I would set up a workflow that would send a (standard, unchanging) email message to people once they are entered into the spreadsheet.

Perfect World Process:

Copy paste data with last,first -> use a "completed" type trigger for a workflow that would take last,first and generate first.last@mycompany.com and would send my standard email message (Message does not change, only recipient names change).

Jason Tarpinian

You can definitly do this, Jennifer! It works by using the LEFT and RIGHT functions along with FIND to search for the comma delimiter. LEFT and RIGHT allow you to grab a certain number of characters in a cell (starting from either the left or right side), and by combining that with the FIND function (returns the character number in the string), you can narrow down to first and last names. The LEN (count total characters in a cell) is used to count from the right.

=RIGHT([Last, First]@row, LEN([Last, First]@row) - FIND(",", [Last, First]@row) - 1) + "." + LEFT([Last, First]@row, FIND(",", [Last, First]@row) - 1) + ".mycompany.com"

Hopefully this works for your scenario!