Sorting e-mail addresses

[Anna Sargent]

I have a large amount of e-mail addresses.
How can I sort them so that I can isolate only
those with @aol.com for instance ?

thanks,
ams

 

[Tom Ogilvy]

set up a helper column
assume the helper cell is E1 and the first email address is in A1

=RIGHT(A1,LEN(A1)-FIND("@",A1))

Then drag fill down the column.

then sort your data with this column as the key.

Regards,
Tom Ogilvy

 

[ams]

Much appreciated, you two.
anna

Try in B1:
=IF(ISERROR(SEARCH("@aol.com",A1)>1),"No Match","An AOL Acct")
And Copy Down..
HTH

 

[Louise Smith]

Hi Anna,

One way would be to parse the e-mail address so "aol.com" is put in a
separate column and then sort your table by this column.

Fortunately, Excel provides a simple way to parse text in a column.

Try this:

- copy the column with your e-mail addresses and Edit > Insert Copied
Cells into the worksheet next to the original column (this way you
still have the unparsed e-mail addresses when you're done)

- select the column you inserted and select Data > Text to Columns

- in the Convert Text to Columns Wizard, select "Delimited" as the
file type

- click Next

- check the "Other" field and insert "@" (without the quotes) in the
box to the right
- none of the other boxes should be checked
- the Text Qualifier can be set to anything

- check the Data Preview to confirm the e-mail addresses will be
parsed with the user name and domain (eg. "aol.com") in separate
columns
- the @ will no longer appear with the domain, but this won't matter
as the domain name is sufficient

- click Finish

- now select the worksheet and sort

You can delete the columns with the parsed data when you're finished
if you wish.

Hope this helps,

Louise