# Discussion on: I use MS Excel 2010. I have a list that conains email and non-email address

4
Comments

### Join the conversation!

Follow via:
RSS
Email Alert
Just In
###### Here is a formula that should do it
johbelnel@... 18th Feb
If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
This should give you the part in front of the "@"
then type this in C1
=IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
This should give you the part between the "@" and the first "."

Hope this helps.
View:
Show:
0 Votes
###### I use MS Excel 2010. I have a list that conains email and non-email address
edwardfowler 18th Jan
I used the FIND and MID commands,using the "@" as the midstring but could only sort the fields to the right, truncating the name on the left. Is there a way to give me both halves of the string (full email address) or another way to do it?
Thanks

Edward
-1 Votes
###### I would convert text to columns using @ as the delimiter.
robo_dev Updated - 18th Jan
I am not an Excel guru, but I would parse/split the data first, then sort on the two halves.

The term for that is creating a 'helper column'
0 Votes
###### Reponse To Answer
edwardfowler 21st Jan
Thanks, but this trucates the field, eliminating everything to the right of the "@"
0 Votes
###### Here is a formula that should do it
johbelnel@... 18th Feb
If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
This should give you the part in front of the "@"
then type this in C1
=IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
This should give you the part between the "@" and the first "."

Hope this helps.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.