last, first to [email protected]

Is there a way to take a column:

Last, First

and build

[email protected]

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 [email protected] 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 [email protected] and would send my standard email message (Message does not change, only recipient names change).

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 07/01/20

    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!

    Jason Tarpinian - Sevan Technology

    Smartsheet Platinum Partner

Help Article Resources