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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,282
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
MAybe this



Excel 2010
AB
2john.smith@abc.com.aujohn.smith
3rita.jones@abc.com.aurita.jones
4helen.blow@abc.tv.com.auhelen.blow
Sheet1
Cell Formulas
RangeFormula
B2=LEFT(A2,FIND("@",A2,1)-1)
 

jim may

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


Excel 2012
A
1My E-mails
2john.smith@abc.com.au
3rita.jones@abc.com.au
4helen.blow@abc.tv.com.au
Sheet1




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



Excel 2012
A
1My E-mails
2john.smith
3rita.jones
4helen.blow
Sheet1
 

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
21,282
Office Version
  1. 365
  2. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,204
Messages
5,835,966
Members
430,398
Latest member
Wookiee_

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