Extracting First Name and Surname from Email Addresses

[Tashat]

Hi everyone

I have a list of email addresses and I need a formula to extract the name and surname from the email address in a separate column. I have shown the ideal output below, but I have typed the name and surname manually in my example below. I have thousands of rows of emails, so this isn't a viable approach. Ideally I want to achieve all these steps of splitting out the first and surname, removing the unwanted characters and the replacing the . with a space all in one formula, as opposed to splitting in two columns and then concatenating in a third. Is this possible? I use Excel 365. Thank you in advance.

Book1
	A	B
1	Email	Name
2	joe.bloggs88@email.com	Joe Bloggs
3	amy.jones15@email.com	Amy Jones
4	matt.smith22@email.com	Matt Smith
5	laura.samson58@email.com	Laura Samson
6	joe.bloggs88@email.com	Joe Bloggs
7	joe.bloggs88@email.com	Joe Bloggs
8	matt.smith22@email.com	Matt Smith
9	matt.smith22@email.com	Matt Smith
Sheet1

 

[sandy666]

Email		Name
joe.bloggs88@email.com		Joe Bloggs
amy.jones15@email.com		Amy Jones
matt.smith22@email.com		Matt Smith
laura.samson58@email.com		Laura Samson
joe.bloggs88@email.com		Joe Bloggs
joe.bloggs88@email.com		Joe Bloggs
matt.smith22@email.com		Matt Smith
matt.smith22@email.com		Matt Smith

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"Email", each Text.BeforeDelimiter(_, "@"), type text}}),
    Replace = Table.ReplaceValue(ETBD,"."," ",Replacer.ReplaceText,{"Email"}),
    Name = Table.AddColumn(Replace, "Name", each Text.Select([Email], {"a".."z", " "})),
    Proper = Table.TransformColumns(Name,{{"Name", Text.Proper, type text}}),
    TSC = Table.SelectColumns(Proper,{"Name"})
in
    TSC

or without duplicates

Name
Joe Bloggs
Amy Jones
Matt Smith
Laura Samson

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"Email", each Text.BeforeDelimiter(_, "@"), type text}}),
    Replace = Table.ReplaceValue(ETBD,"."," ",Replacer.ReplaceText,{"Email"}),
    Name = Table.AddColumn(Replace, "Name", each Text.Select([Email], {"a".."z", " "})),
    Proper = Table.TransformColumns(Name,{{"Name", Text.Proper, type text}}),
    Distinct = Table.Distinct(Proper, {"Name"}),
    TSC = Table.SelectColumns(Distinct,{"Name"})
in
    TSC

 

[Candyman8019]

for a formula, you could use this:
=SUBSTITUTE(LEFT(K21,FIND("@",K21)-1),"."," ")

In this example, the email address is in K21

 

[Tashat]

for a formula, you could use this:
=SUBSTITUTE(LEFT(K21,FIND("@",K21)-1),"."," ")

In this example, the email address is in K21

That's great! Thank you. Is there a way of changing it to find the first instance of a number and returning the characters before the numbers, so that it excludes the two digits after the surname? I've been playing with {0,1,2,3,4,5,6,7,8,9} but this still isn't working for me. Thank you.

 

[Tashat]

for a formula, you could use this:
=SUBSTITUTE(LEFT(K21,FIND("@",K21)-1),"."," ")

In this example, the email address is in K21

Email		Name
joe.bloggs88@email.com		Joe Bloggs
amy.jones15@email.com		Amy Jones
matt.smith22@email.com		Matt Smith
laura.samson58@email.com		Laura Samson
joe.bloggs88@email.com		Joe Bloggs
joe.bloggs88@email.com		Joe Bloggs
matt.smith22@email.com		Matt Smith
matt.smith22@email.com		Matt Smith

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"Email", each Text.BeforeDelimiter(_, "@"), type text}}),
    Replace = Table.ReplaceValue(ETBD,"."," ",Replacer.ReplaceText,{"Email"}),
    Name = Table.AddColumn(Replace, "Name", each Text.Select([Email], {"a".."z", " "})),
    Proper = Table.TransformColumns(Name,{{"Name", Text.Proper, type text}}),
    TSC = Table.SelectColumns(Proper,{"Name"})
in
    TSC

or without duplicates

Name
Joe Bloggs
Amy Jones
Matt Smith
Laura Samson

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETBD = Table.TransformColumns(Source, {{"Email", each Text.BeforeDelimiter(_, "@"), type text}}),
    Replace = Table.ReplaceValue(ETBD,"."," ",Replacer.ReplaceText,{"Email"}),
    Name = Table.AddColumn(Replace, "Name", each Text.Select([Email], {"a".."z", " "})),
    Proper = Table.TransformColumns(Name,{{"Name", Text.Proper, type text}}),
    Distinct = Table.Distinct(Proper, {"Name"}),
    TSC = Table.SelectColumns(Distinct,{"Name"})
in
    TSC

Thank you so much for doing this! This looks so complicated. Ideally I'd like to avoid using a power query, just because the spreadsheet is enormous and I'm not very confident with using them yet. Thank you though.

 

[Fluff]

how about

+Fluff v2.xlsm
	A	B
1	Email	Name
2	joe.bloggs88@email.com	Joe Bloggs
3	amy.jones15@email.com	Amy Jones
4	matt.smith22@email.com	Matt Smith
5	laura.samson58@email.com	Laura Samson
6	joe.bloggs88@email.com	Joe Bloggs
7	joe.bloggs88@email.com	Joe Bloggs
8	matt.smith22@email.com	Matt Smith
9	matt.smith22@email.com	Matt Smith
Master

Cell Formulas
Range	Formula
B2:B9	B2	=PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(SEQUENCE(10,,0),A2&SEQUENCE(10,,0)))-1),"."," "))

 

[sandy666]

This looks so complicated. Ideally I'd like to avoid using a power query, just because the spreadsheet is enormous

This is not so complicated
but if you prefer thousands formulas (increasing file size) - good luck
have a nice day

 

[Fluff]

The formula in post#6 won't work if you don't have any numbers, use this instead

=PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(SEQUENCE(10,,0),SUBSTITUTE(A2,"@",9)&SEQUENCE(10,,0)))-1),"."," "))

or if you have the new LET function

=LET(Seq,SEQUENCE(10,,0),PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(Seq,SUBSTITUTE(A2,"@",9)&Seq))-1),"."," ")))