3

I got a list of mail addresses, let's say we have the list

thisisanexample@home.com
anotherthing@whatever.com
hello.world@idontknow.com
default@idk.com
youleave@whatever.com
nomoreideas@sorry.com
example@g.com
another_option@home.com
efg@idk.com

In Excel, how can I set up 2 rules of sorting? I like the first sort rule say that we sort alphabetically after the @ symbol.

The second sorting rule says that if the part after the @ symbol is same, then sort the part before the @ alphabetically.

So basically, the list above would look like that if we applied these 2 sorting rules:

example@g.com
another_option@home.com
thisisanexample@home.com
default@idk.com
efg@idk.com
hello.world@idontknow.com
nomoreideas@sorry.com
anotherthing@whatever.com
youleave@whatever.com

I know how to set up the first rule in Excel at least. It's done by selecting the column to be sorted, copying it twice to another two columns, then in the second column select all mail addresses, press "Text in Columns" the seperate the mails when you see the symbol @. Then you have in the very first column the actual mail address, the second column you have the part before the @, the third column you have the part after the @. Now just let the column of the after @ part sort and you are done.

But I have no idea how to take both rules into account at the same time...? :S

I really hope my question is clear because else I will have a very long day tomorrow at work :P

5

You're almost there, you need to setup a second level of sorting (by clicking on "Add level":

enter image description here

The sorted e-mails:

enter image description here

Note that each level's sort order is independent of the other levels

  • Thanks so much this is amazing and will save me a lot time tomorrow and probably also in the future ! :) – cnmesr Aug 28 '18 at 20:47
1

You can set up cell B1 as Mid(A1, Pos(A1, '@') + 1, Len(A1) - Pos(A1, '@') and then copy it down the column. Then copy column B and paste-by-value onto itself. Then sort using column B (email domains) as top level and column A as second level.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.