# Extracting First Name and Surname from Email Addresses

#### Tashat

##### Board Regular
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
AB
1EmailName
2joe.bloggs88@email.comJoe Bloggs
3amy.jones15@email.comAmy Jones
4matt.smith22@email.comMatt Smith
5laura.samson58@email.comLaura Samson
6joe.bloggs88@email.comJoe Bloggs
7joe.bloggs88@email.comJoe Bloggs
8matt.smith22@email.comMatt Smith
9matt.smith22@email.comMatt Smith
Sheet1

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### sandy666

##### Banned - Rules violations
 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

Power Query:
``````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

Power Query:
``````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``````

Last edited:

#### Candyman8019

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

In this example, the email address is in K21

#### Tashat

##### Board Regular
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

##### Board Regular

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

Power Query:
``````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

Power Query:
``````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

##### MrExcel MVP, Moderator
+Fluff v2.xlsm
AB
1EmailName
2joe.bloggs88@email.comJoe Bloggs
3amy.jones15@email.comAmy Jones
4matt.smith22@email.comMatt Smith
5laura.samson58@email.comLaura Samson
6joe.bloggs88@email.comJoe Bloggs
7joe.bloggs88@email.comJoe Bloggs
8matt.smith22@email.comMatt Smith
9matt.smith22@email.comMatt Smith
Master
Cell Formulas
RangeFormula
B2:B9B2=PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(SEQUENCE(10,,0),A2&SEQUENCE(10,,0)))-1),"."," "))

#### sandy666

##### Banned - Rules violations
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

##### MrExcel MVP, Moderator
The formula in post#6 won't work if you don't have any numbers, use this instead
Excel Formula:
``=PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(SEQUENCE(10,,0),SUBSTITUTE(A2,"@",9)&SEQUENCE(10,,0)))-1),"."," "))``
or if you have the new LET function
Excel Formula:
``=LET(Seq,SEQUENCE(10,,0),PROPER(SUBSTITUTE(LEFT(A2,MIN(FIND(Seq,SUBSTITUTE(A2,"@",9)&Seq))-1),"."," ")))``

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,678
Messages
5,838,736
Members
430,566
Latest member
ChanchalSingh

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back