Name to Email with exception

Hello,

I have a function that turns a cell with a name into an email address for supervisors. However, some supervisors are in training and have an abbreviation at the end of their name, is there a way to come to the same email result? The cell text would be

First Last (T1)

(number after T is different for each person)


=IFERROR(LEFT(Supervisor@row, FIND(" ", Supervisor@row) - 1) + "." + RIGHT(Supervisor@row, (LEN(Supervisor@row) - FIND(" ", Supervisor@row))) + "@gmail.com", "")

Answers

  • Krissia B.
    Krissia B. Moderator

    Hello @Desertbird

    Thank you for your post! Upon reviewing & testing, I recommend using the formula below:

    • =SUBSTITUTE(Supervisor@row, " ", ".") + "@gmail.com"

    I had thought that instead of picking out each word, you could use the SUBSTITUTE function to replace all the " " with "."


    See my screenshot below.


    Hope this helps!

    Cheers,

    Krissia

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!