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

B

Bucky

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

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.
 
D

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.
 
D

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.
 
B

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.
Click to expand...

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

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.
Click to expand...

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

 
R

Ron Rosenfeld

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

For example:

Before sorting:
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

After sorting:
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

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

Thanks.


Z. D.
Click to expand...

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
 
R

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
Click to expand...


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
 
Top