extract name from email address

Hi there,

I have a created by column that captures the email address : nero.naidoo@angloamerican.com


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 ✓

    @Nero,

    Please try the following:


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

    1- Extracted Name

    =IFERROR(LEFT(Email@row, FIND("@", Email@row) - 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

    bassam.khalil2009@gmail.com

    ☑️ 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"

«1

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Nero

    I hope you're well and safe!

    Will the email always be in the same format (First.Lastname@domain.com)?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • hi Andree',


    yes, that's the standard that my company uses.

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

    Hi @Nero

    Hope you are fine, please try the following formula:

    =IFERROR(LEFT(Email@row, FIND("@", Email@row) - 1), "")
    

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • hi @Bassam Khalil ,

    thank you for your response. I have tried that. however, I'm looking for a formula that will capitalize the first letter of the name and last name and remove the "." So for the second example, it must return Nero Naidoo, not nero.naidoo

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Nero

    I need yo know each name will contain "." between the First Name and the Last Name?

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

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

    @Nero,

    Please try the following:


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

    1- Extracted Name

    =IFERROR(LEFT(Email@row, FIND("@", Email@row) - 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

    bassam.khalil2009@gmail.com

    ☑️ 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"

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

    @Nero 

    You are welcome and i will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • @Bassam Khalil,


    Feel like I'm taking advantage now, but I do have one more problem I'm struggling with.

    I have a workflow that will copy a row to another sheet when the status changes to Re-open. I've done this so that I can count the number of times a ticket gets re-opened after it has been completed (testing quality). This seems to work fine if the ticket has been re-opened once, but when it's re-opened the second time, it seems to create an additional row with the same ticket number instead of updating the status. In this instance, the ticket is re-opened by the requester where the email address is captured through the systems created by the function and does not have shared access to the sheet. they submit a request through a form and re-open through the open update form link.

    I have a similar workflow for when the ticket is rejected by a reviewer and it seems to work fine - in this instance the reviewer's email address is entered with a drop-down selection and has shared access to the sheet.

    Any thoughts on what could be causing this?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Nero Naidoo

    Could you please share me as an admin on a copy of your sheet after removing any sensitive data and I will check it for you.please stay wake up for my conversation on the shared sheets if you share me.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nero Naidoo

    I saw that Bassam answered already!

    Let me know if I can help with anything else!

    Regarding the issue with the Workflow.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • @Bassam Khalil ,


    Thank you. I have shared. It's all test data. I've shared the workspace so you can see where it's copying. the other thing I've noticed is that, every time the ticket is re-opened for the 2nd time, it disables the copy row workflow. Let me know if it will be better to collaborate over a call.

  • @Andrée Starå ,


    Thank you. Bassam did answer and it worked:)

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Nero Naidoo

    Ok, let me check it first then will talk.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Nero Naidoo

    Excellent!

    You're more than welcome!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!