extract first and last name from email adress

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,525
I would like a formula to extraxt the first and last name from an email address and as well as for the first and last name to start with upper case for e.g email address patrick.davids@syke.pl.com


this should appear as Patrick Davids using a formula



your assistance in this regard is most appreciated
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,382
Office Version
365
Platform
Windows
Hi, one (of probably many) option.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">patrick.davids@syke.pl.com</td><td style="background-color: #FFFF00;;">Patrick Davids</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=PROPER(<font color="Blue">SUBSTITUTE(<font color="Red">LEFT(<font color="Green">A1,FIND(<font color="Purple">"@",A1</font>)-1</font>),"."," "</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,686
Office Version
365
Platform
Windows
How about
=PROPER(SUBSTITUTE(LEFT(A2,FIND("@",A2)-1),"."," "))

Beaten 2it
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,686
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,636
Messages
5,445,651
Members
405,348
Latest member
jo_hivera

This Week's Hot Topics

Top