Creating an email out of a full name

Options

Hi,

I am trying to make an email out of a full name cell and am running into issues when trying the Right/Left method (ex: =RIGHT(A2,LEN(A2)-FIND(" ",A2,1))  and =LEFT(A2,SEARCH(" ",A2)).

When trying the above method, the Left would not work (was going to split them up and bring back together with CONCATENATE).

All emails on this sheet follow the same pattern: "First"."Last"@gmail.com like the example below. Is there a reason the =left function is not working? Is there a better method for Smartsheets?

Thanks for any input!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Desertbird

    Try this:

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


    You'll want to find the Space between the first and last name in the Name cell and use that (minus 1) as the starting position for the LEFT and RIGHT functions. Then you can add together the other elements of an email with the + symbol.

    Let me know if this works!

    Cheers,

    Genevieve

  • Desertbird
    Options

    Hi, Yes thank you this worked, however it left a space between the period and the last name, is there a way to remove this?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Desertbird

    My apologies! We need to use something slightly different for the RIGHT portion of the formula. To find the character to start returning values from, we can use LEN to calculate the number of characters in the cell, then minus the number of characters from the left of the space.

    Try this:

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


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!