extract first and last name from email adress

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,534
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,405
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
40,534
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
40,534
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,098,873
Messages
5,465,211
Members
406,419
Latest member
ldp124

This Week's Hot Topics

Top