extract name from email address

Nero Naidoo · 07/31/21

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

Bassam Khalil · 08/02/21

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

Andrée Starå · 07/31/21

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!

Nero Naidoo · 07/31/21

hi Andree',

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

Bassam Khalil · 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

Nero Naidoo · 08/02/21

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 · 08/02/21

@Nero

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

Bassam Khalil · 08/02/21

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

Nero Naidoo · 08/02/21

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

Bassam Khalil · 08/02/21

@Nero

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

Nero Naidoo · 08/02/21

@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 · 08/02/21

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

Andrée Starå · 08/02/21

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

Nero Naidoo · 08/02/21

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

Nero Naidoo · 08/02/21

@Andrée Starå ,

Thank you. Bassam did answer and it worked:)

Bassam Khalil · 08/02/21

@Nero Naidoo

Ok, let me check it first then will talk.

Andrée Starå · 08/02/21

@Nero Naidoo

Excellent!

You're more than welcome!

extract name from email address

Best Answer

Answers

Help Article Resources

Categories