How can I extract email addresses within text

I have email addresses within long text strings in cells which I need to extract - the email address can be anywhere in the string but can be identified by the @ then a space before the start and a space after eg: from the below example:

asdfh ald lksdfh aldjk [email protected] aksdfh alkjdh need [email protected]
akdflajds hflakjhdflkahsdkh [email protected] asdkfh aksdh faldj need [email protected]
asdkhf alksdjh fa [email protected] ksdhf askdjh faldj need [email protected]

Can anyone kindly point me in the direction to extract the email addresses on its own into a new cell?

Try this...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT(" ",LEN(A1))),LEN(A1)))
Note - if you have more than 1 email address in same cell..it will extract the first email address...

that is fantastic you are very helpful.

I have just noticed that some email addresses have a colon : at the beginning not a space - I don't suppose you can search for a : or a space?????

Please forget the last question, I can do that myself but I have one other if thats OK?

If I have text in a cell A1 with lets say ABC on the end that I do not want how can I get the text without the ABC. The lengths of the strings will very.

So

a sueabc I want sue
b fredbc I want fred
c georgeabc I want george

This is my last question.

Originally Posted by sueryan13

Please forget the last question, I can do that myself but I have one other if thats OK?

If I have text in a cell A1 with lets say ABC on the end that I do not want how can I get the text without the ABC. The lengths of the strings will very.

So

a sueabc I want sue
b fredbc I want fred
c georgeabc I want george

This is my last question.

May be u can replace it with blank by Ctrl+H..( Replace ABC with blank)..hope this helps..