extract name from email address

Nero Naidoo

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

Bassam Khalil

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