Extracting First Name and Surname from Email Addresses

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
137
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
Upvote 0
for a formula, you could use this:
=SUBSTITUTE(LEFT(K21,FIND("@",K21)-1),"."," ")

In this example, the email address is in K21
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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),"."," "))
 
Upvote 0
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)
 
Upvote 0
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),"."," ")))
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,629
Members
452,661
Latest member
Nonhle

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
Back
Top