extract name from email address

Hi there,

I have a created by column that captures the email address : [email protected]

I want to create a formula that will create a name column that returns Nero Naidoo. I've tried some of the split-to-text formulas suggested and seem to only come up with nero.naidoo. I want to capitalize the "N" and replace the "." with space. any ideas?

Note that the email address may vary I length so I cant use a fixed character number.

Thank you in advance for your assistance

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/02/21 Answer ✓


    Please try the following:

    Add the following columns with the following formulas ( Alll formulas are column format formula):

    1- Extracted Name

    =IFERROR(LEFT([email protected], FIND("@", [email protected]) - 1), "")

    2- Dot Location

    =FIND(".", [Extracted Name]@row)

    3- Name Len

    =LEN([Extracted Name]@row)

    4- Formatted Full Name

    =UPPER(LEFT([Extracted Name]@row, 1)) + MID([Extracted Name]@row, 2, [Dot Location]@row - 2)
    + " " + UPPER(MID([Extracted Name]@row, [Dot Location]@row + 1, 1)) + MID([Extracted Name]@row
    , [Dot Location]@row + 2, [Name Len]@row - [Dot Location]@row)

    PMP Certified

    [email protected]


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!