last, first to firstname.lastname@mycompany.com
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).
Answers
-
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 Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!