Extracting First Name and Surname from Email Addresses

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
EmailName
joe.bloggs88@email.comJoe Bloggs
amy.jones15@email.comAmy Jones
matt.smith22@email.comMatt Smith
laura.samson58@email.comLaura Samson
joe.bloggs88@email.comJoe Bloggs
joe.bloggs88@email.comJoe Bloggs
matt.smith22@email.comMatt Smith
matt.smith22@email.comMatt 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

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 12, 2005
Messages
65
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

In this example, the email address is in K21
EmailName
joe.bloggs88@email.comJoe Bloggs
amy.jones15@email.comAmy Jones
matt.smith22@email.comMatt Smith
laura.samson58@email.comLaura Samson
joe.bloggs88@email.comJoe Bloggs
joe.bloggs88@email.comJoe Bloggs
matt.smith22@email.comMatt Smith
matt.smith22@email.comMatt 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
Joined
Jun 12, 2014
Messages
55,250
Office Version
  1. 365
Platform
  1. Windows
how about
+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
Joined
Oct 24, 2015
Messages
7,499
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 (y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,250
Office Version
  1. 365
Platform
  1. Windows
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),"."," ")))
 

Watch MrExcel Video

Forum statistics

Threads
1,126,981
Messages
5,621,958
Members
415,869
Latest member
LWSkinner

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top