Extracting part of and email address

zyosis

New Member
Joined
Aug 27, 2014
Messages
8
Hi,
I was wondering if there was an easy way to extract the first part of an email address?

john.smith@abc.com.au
rita.jones@abc.com.au
helen.blow@abc.tv.com.au

I am currently using formula that cuts off the 11 characters from the right, then have to go over them, incase there was a longer email in thre (ie 3rd example).

I am pretty sure that someone I worked with last job, could extract the first part of the email.

--btw: the firstpart of the email (before @) is part of the unique login details, hence why trying to extract.
Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,201
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
MAybe this


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-decoration: underline;color: #0000FF;;">john.smith@abc.com.au</td><td style=";">john.smith</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-decoration: underline;color: #0000FF;;">rita.jones@abc.com.au</td><td style=";">rita.jones</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-decoration: underline;color: #0000FF;;">helen.blow@abc.tv.com.au</td><td style=";">helen.blow</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">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: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=LEFT(<font color="Blue">A2,FIND(<font color="Red">"@",A2,1</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
with Sheet1 looking like so...

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">My E-mails</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-decoration: underline;color: #0563C1;;">john.smith@abc.com.au</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-decoration: underline;color: #0563C1;;">rita.jones@abc.com.au</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-decoration: underline;color: #0563C1;;">helen.blow@abc.tv.com.au</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />



Code:
Sub Foo()
Dim LR As Long, Rng As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A2:A" & LR)
    For Each C In Rng
        C.Hyperlinks.Delete
        C.Value = Left(C, WorksheetFunction.Find("@", C) - 1)
    Next C
End Sub

Then Run this Macro:

Afterwards sheet1 should look like this


<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">My E-mails</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">john.smith</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">rita.jones</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">helen.blow</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

zyosis

New Member
Joined
Aug 27, 2014
Messages
8

ADVERTISEMENT

Hi Michael,
Thanks very much, this worked for my purposes.
Would you please be able to break down the explanation of the formula (I am really good at using the SUM function....learning to break out here =).
Thanks,
 

zyosis

New Member
Joined
Aug 27, 2014
Messages
8
Hi Jim,
Thanks for the advice. VBA and macros are a little more advanced than where I am at at the moment, but working on getting there =).
I will revisit your code when there.
Thanks for taking the time to post.
Shannon
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,201
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Okay
LEFT(A2.......we want to find something in the left of the strig in A2
FIND("@",A2,1)....the something we want to find is to the LEFT of the @ symbol and it is in cell A2 and the search is to start at the first character on the left
-1)......when we find the string we are looking for,it will include the @ symbol.....so to exclude that symbol we use -1 or the found string LESS one character

If you go to the formula tab>>formula auditing>>Evaluate formula....it will step through the formula bit by bit and show / explain the results on the fly

Does that help ?
 

zyosis

New Member
Joined
Aug 27, 2014
Messages
8
Thanks Michael,
Yes, and I will also do the step through process to reinforce.
8oD
 

Watch MrExcel Video

Forum statistics

Threads
1,130,311
Messages
5,641,453
Members
417,210
Latest member
rins

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