Email address appears twice in cell info

[dyanblak]

Hi all.
This in an annoying little problem which I am hoping there is a simple solution for by way of a formula or text to columns manipulation.
My raw data which is produced into a single cell is supposed to contain a name and then 2 spaces and an email address. Some of the cells contain the email address repeated twice in error. I need just the name and a single email address.

If eg cell A1 contains:
James Smith jsmith@ultra.com jsmith@ultra.com

How can I get rid of the duplication? Any suggestions GREATLY appreciated.
Thanks

 

[sparky2205]

Good morning,
give this a go:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=2,LEFT(A1,FIND(" ",A1,FIND("@",A1,FIND("@",A1)))-1),A1)
You should be able to copy this down your data and it takes account of single or duplicate entries

 

[dyanblak]

Good morning,
give this a go:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"@",""))=2,LEFT(A1,FIND(" ",A1,FIND("@",A1,FIND("@",A1)))-1),A1)
You should be able to copy this down your data and it takes account of single or duplicate entries

Thank you Sparky - that is perfect. Wizardry indeed - much appreciated as will save me hours of manually tidying the spreadsheets

 

[Peter_SSs]

This should also do it for you for singles or duplicates (or triplicates ...).

=LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)