Extracting part of and email address

[zyosis]

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

 

[Michael M]

MAybe this

Excel 2010
	A	B
2	john.smith@abc.com.au	john.smith
3	rita.jones@abc.com.au	rita.jones
4	helen.blow@abc.tv.com.au	helen.blow
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("@",A2,1)-1)

 

[Fazza]

Or maybe data to text. Wizard via keyboard : ALT-D-E

 

[jim may]

with Sheet1 looking like so...

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

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
1	My E-mails
2	john.smith
3	rita.jones
4	helen.blow
Sheet1

 

[zyosis]

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]

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]

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]

Thanks Michael,
Yes, and I will also do the step through process to reinforce.
8oD