How do I sort in Excel by a root e-mail like @yahoo.com?

R

Roccy.

I'd like to be able to sort in Excel by a root e-mail like @yahoo.com?

I have a large data base of e-mails and I want to sort them by the root
e-mail address and can't figure out how.
 
H

Haldun Alay

Add one column that contains following formula...

Assume that e-mail addresses in Column A and starts from Cell A2...
(change A2 part with your needs)

=RIGHT(A2,LEN(A2)-SEARCH("@",A2))

and sort your table with that column...
 
M

Mike H

You need a couple of helper columns. Assuming your email addresses are in
column A in another column (Say B) type the formula:-

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

In another column (Say C) type the formula:-

=MID(A1,FIND("@",A1,1),99)
Drag down as far as necessary

Select the 3 columns then sort by column C and then by column B and your done.

Mike
 
R

Roccy.

when I used the formula you gave me, it pulled the root e-mail out of column
A and not the entire e-mail.

Imagine if you will 5000 e-mails in column A and few are the same. However,
10-20-30 have the same root after the @ symbol. I want to sort column A to
put a specific group of e-mails with the same @ symbol together. If I had 10
@hotmail.com addresses, I'd want to pull them from the 5000 and put them at
the top of the 5000 list.

your help is much appreciated.
 
H

Haldun Alay

1. Assuming e-mail addresses in column A,
2. In column B, cell B2 insert the formula... and if you want @ sign also
use this
=RIGHT(A2,LEN(A2)-SEARCH("@",A2)+1)
3. sort your table by Column B, then by Column A

then you will get e-mail addresses sorted by domain
 
R

Roccy.

I know you are trying to be helpful, but putting either formula just pulls up
the @------. It does not pull the entire e-mail and I don't understand how
to search the entire 5000 e-mails in the A column to pull out the specific
ones I want.
 
R

Roccy.

Haldun Alay said:
1. Assuming e-mail addresses in column A,
2. In column B, cell B2 insert the formula... and if you want @ sign also
use this
=RIGHT(A2,LEN(A2)-SEARCH("@",A2)+1)
3. sort your table by Column B, then by Column A

then you will get e-mail addresses sorted by domain
 
H

Haldun Alay

After inserting the formula, select B2:B5001 and use Edit|Fill|Down from
menu (or Ctrl-D)

Then sort by column B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top