Sorting emails by domains, from org to edu (right char is the most significant)

[duzhidian]

Hello All
I need to sort the domains according their emails.

For example:

Before sorting:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

After sorting:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

That is, how to sort, according to the domain name ( the right is the
most significant )?

Thanks.


Z. D.

 

[Bucky]

That is, how to sort, according to the domain name ( the right is the
most significant )?

you'll probably need to create some helper columns. use text parsing
functions like SEARCH, LEFT, MID, RIGHT to separate the different
fields you need. then do a multiple sort.

 

[Dave Peterson]

Insert an empty column to the right of your data
Select your column
Data|Text to columns
delimited by other (@)
and skip the first field

Then sort your range using that adjacent column as the primary key. And the
original column as the secondary key.

 

[Dave Peterson]

I didn't notice that portion.

I'd still use the data|text to columns, but then I'd use another column with
something like:

=if(right(b1,4)=".org",1,if(right(b1,4)=".com",2,if(right(b1,4)=".edu",3,4)))

And then use that numeric column as the primary key, the domain column for the
secondary key and the original column as the tertiary key.

 

[Bruce Sinclair]

Insert an empty column to the right of your data
Select your column
Data|Text to columns
delimited by other (@)
and skip the first field

Then sort your range using that adjacent column as the primary key. And the
original column as the secondary key.

... and if you want the org/com/edu sort order, you'll probably need a
custom list as well.

 

[Bruce Sinclair]

I didn't notice that portion.

I'd still use the data|text to columns, but then I'd use another column with
something like:

=if(right(b1,4)=".org",1,if(right(b1,4)=".com",2,if(right(b1,4)=".edu",3,4)))

And then use that numeric column as the primary key, the domain column for the
secondary key and the original column as the tertiary key.

Code the helper column ... yep ... that will work ... and is probably a
better long term solution. Custom lists can be a pain, popping up when they
are least expected

Thanks

 

[Ron Rosenfeld]

Hello All
I need to sort the domains according their emails.

For example:

Before sorting:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

After sorting:
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)

That is, how to sort, according to the domain name ( the right is the
most significant )?

Thanks.


Z. D.

If you want to do this using a formula approach, you could download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/

Then use this **array** formula (enter the formula by holding down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula).

=INDEX(VSORT(rng,MID(rng,FIND(".",rng,FIND("@",rng))
+1,255),1,MID(rng,FIND("@",rng)+1,-1+FIND(".",rng,
FIND("@",rng))-FIND("@",rng)),1),ROWS($1:1))

Copy/drag down as far as necessary.
--ron

 

[Ron Rosenfeld]

If you want to do this using a formula approach, you could download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/

Then use this **array** formula (enter the formula by holding down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula).

=INDEX(VSORT(rng,MID(rng,FIND(".",rng,FIND("@",rng))
+1,255),1,MID(rng,FIND("@",rng)+1,-1+FIND(".",rng,
FIND("@",rng))-FIND("@",rng)),1),ROWS($1:1))

Copy/drag down as far as necessary.
--ron

Oh, dummy me. Since you'd be downloading morefunc anyway, the following is
more efficient and does NOT need to be entered as an array formula:

=INDEX(VSORT(rng,REGEX.MID(rng,"[^\.]+$"),1,REGEX.MID(rng,"@[^\.]+",1)),ROWS($1:1))

--ron