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 ✓

    @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 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]

    www.mobilproject.it

    ☑️ 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 ([email protected])?

    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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • 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 protected], FIND("@", [email protected]) - 1), "")
    

    the following screenshot shows the result


    PMP Certified

    [email protected]

    www.mobilproject.it

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

    [email protected]

    www.mobilproject.it

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

    www.mobilproject.it

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

  • Brilliant!!!!!, thank you @Bassam Khalil

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

    @Nero 

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

    PMP Certified

    [email protected]

    www.mobilproject.it

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

    [email protected]

    www.mobilproject.it

    ☑️ 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, [email protected])

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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

    [email protected]

    www.mobilproject.it

    ☑️ 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: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Help Article Resources