excel sorting

quinnd6

If I have a list of email addresses in excel how do I sort by the domain name?

So eg. if I had john@b.ie ,john@a.ie , and john@c.ie
Id have
john@a.ie
john@b.ie
john@c.ie

Evil Phil

Select the data you want to sort then on the Data menu select Sort ... Now choose the columns your sort criteria and click on Ok.

EL_Loco

That'll arrange it by the name, not the domain name.

you should do something like splitting out the domain.

copy the email column into a 2nd column, highlight it, then click on the data menu and choose "text to columns"
delimited, click next
tick "other" and put in @ as the delimiter, then "finish"

this will split the name and the domain name, highlight all your data and do the sort by the domain name column.

messy, but there ya go.

edit: you example is bad also as all first names are the same,

something like adam@z.com you would want last, because the domain name is "z" right?

shanew

assuming your email addresses are in column 'B' and start
in row 4 then paste the formula below to the entire
column 'C' - adjust the ref if required.

This extracts everything after the @ to that column
which you can then title 'Domain' and sort on that.

=MID(B4,1+FIND("@",B4,1),32)


hth...


Shane

Zynks

I am sure there is a way to do it inside Excel, but if you don't find it and you really need to get it done, here is another rather complicated way, but it works):

- Copy the whole table to Word
- Copy the column with the email addresses and paste it lower in the page
- Convert table to text with paragraph marks to separate entries
- Convert back to table with "@" as column delimiter - so you will end with two columns
- Copy the column with the domain names and paste it to a new column at the end of the original table
- Sort the whole table by that column (you can do it inside Word or copy it back to Excel first)

Consultancy invoice will follow

quinnd6 wrote: »

If I have a list of email addresses in excel how do I sort by the domain name?

So eg. if I had john@b.ie ,john@a.ie , and john@c.ie
Id have
john@a.ie
john@b.ie
john@c.ie

kincsem

Too late to help but some might find this tip useful.


Copy the full email addresses in column A to both column B and column C.

Select column B.
Hit Ctrl-H (for Edit & Replace).
In the Find What: box type @*
In the Replace With: box do not type anything. Then hit Replace All. This replaces "@ and everything behind it" with "nothing".

Select column C.
Hit Ctrl-H (for Edit & Replace).
In the Find What:
In the Replace With: box do not type anything. Then hit Replace All. This replaces "@ and everything before it" with "nothing".


The result will be this data.

.....A...................B.............C

[email]alan@f.ie.........alan.........f.ie[/email]
[email]john@b.ie........john.........b.ie[/email]
[email]peter@a.ie.......peter.......a.ie[/email]


Now sort your three columns of data on the column C.

This tip is also useful if you want to sort a list of firstname lastname on the lastname. In this case you use " *" and "* " for the replaces (blank asterisk / asterisk blank). You can do this in seconds. Using formulas to find the space takes longer.

Seth Brundle

In the OPs case, doing that to column B wasn't necessary.

Capt'n Midnight

just use notepad to replace @ with comma and then import as CSV

Seth Brundle

Thats a bit extreme though Capt'n

Capt'n Midnight

sorry /my bad
import the list into excel as a text file, using @ as a delimiter