Need to remove all from cell except email address. How? Please help.

[sapkota]

I have hundreds of cells in excel with email, name and numbers in each of them such as an example below. I would like to remove all and only keep the email address. Please let me know if there is a formula or easy way of doing this.
For example 'Sarendra Maharjan sanjeebsabina@Hotmail.com 4534232r23'
I want only sanjeebsabina@Hotmail.com from the above cell.
Thank you in advance,
Sapkota

 

[Hui]

Sapkota

Firstly, Welcome to the Chandoo.org Forums

Assuming your data is in Column A2 and below
in A2:
=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,FIND(" ",A2,FIND("@",A2))-FIND(" ",A2,FIND(" ",A2)+1)-1)
Copy down

 

[Mike H..]

I have hundreds of cells in excel with email, name and numbers in each of them such as an example below. I would like to remove all and only keep the email address. Please let me know if there is a formula or easy way of doing this.
For example 'Sarendra Maharjan sanjeebsabina@Hotmail.com 4534232r23'
I want only sanjeebsabina@Hotmail.com from the above cell.
Thank you in advance,
Sapkota

This should be quite flexible regarding to number of words, spaces etc before and after the email address. This is for A2, drag down as required.


=TRIM(MID(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),FIND(REPT("@",1),SUBSTITUTE(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),"@",REPT("@",1),1))-40,80))

 

[sapkota]

Sapkota

Firstly, Welcome to the Chandoo.org Forums

Assuming your data is in Column A2 and below
in A2:
=MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,FIND(" ",A2,FIND("@",A2))-FIND(" ",A2,FIND(" ",A2)+1)-1)
Copy down

Hi Hui,
It works like a charm! Thank a whole bunch. I have a follow up question. My data is from E8 to E 4533 with several blank cells in between. How do I go about approaching this if I only want to email address from the example I gave. Thanks again for your prompt and useful response.
Sapkota

 

[Hui]

Replace A2 with E8
Then copy down

Or try:
=MID(E8,FIND("-",SUBSTITUTE(E8," ","-",2))+1,FIND(" ",E8,FIND("@",E8))-FIND("-",SUBSTITUTE(E8," ","-",2))-1)

 

[sapkota]

This should be quite flexible regarding to number of words, spaces etc before and after the email address. This is for A2, drag down as required.


=TRIM(MID(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),FIND(REPT("@",1),SUBSTITUTE(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),"@",REPT("@",1),1))-40,80))

This works really well. But my excel sheet has two issues.
1) It contains several blank rows between data.
2) And in some rows the data are in different length. For example 'satendta bhagat bhagat@....., r32434121' and dofodo Raut rautd@.....' in another. I

Is there a way I could address all these with a formula? Thanks in advance.
Sapkota

 

[sapkota]

Replace A2 with E8
Then copy down

Or try:
=MID(E8,FIND("-",SUBSTITUTE(E8," ","-",2))+1,FIND(" ",E8,FIND("@",E8))-FIND("-",SUBSTITUTE(E8," ","-",2))-1)

Excellent thanks very much. My excel sheet has several blank rows in between data and data are in different length in some rows. Is there a way to address this? Thanks again.

 

[Mike H..]

This works really well. But my excel sheet has two issues.
1) It contains several blank rows between data.
2) And in some rows the data are in different length. For example 'satendta bhagat bhagat@....., r32434121' and dofodo Raut rautd@.....' in another. I

Is there a way I could address all these with a formula? Thanks in advance.
Sapkota

Hi,
The formula should pull out the email no matter what appears before or after and if there's only am email in the cell. To handle blank rows try this alternative that returns a blank cell.


=IF(A2="","",TRIM(MID(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),FIND(REPT("@",1),SUBSTITUTE(SUBSTITUTE(" "&$A2&" "," ",REPT(" ",40)),"@",REPT("@",1),1))-40,80)))