Sort a column of email addresses by end portion of address

[Jammie]

Hi
I am trying to sort a column of email addresses by the end part of the
address eg john.smith @google.ac.uk the .ac.uk part.

Does anyone know an easy way to do this..?

Thanks

 

[Phil Sharpe]

You could create a column using something like:
MID(A1, SEARCH("@", A1) + 1, LEN(A1) - SEARCH("@", A1))

 

[Guest]

Hi Jammie - if you only want the .ac.uk part not @google.ac.uk this will do
it ---
just change the @ to .

MID(C3, SEARCH(".", C3) + 1, LEN(C3) - SEARCH(".",C3))

Yours - Dika

 

[Jammie]

thanks!
Jan

You could create a column using something like:
MID(A1, SEARCH("@", A1) + 1, LEN(A1) - SEARCH("@", A1))

 

[Jammie]

Thanks very much Kernow Girl, that is exactly what I want to do.
However I am a
bit of a novice at this. I have copied my column and replaced all the
@s with the formula you gave me, how do I now get the part after the
bracket of your formula to go into a seperate column so that I can sort
it (or am i doing this the wrong way round?)
Thanks

 

[Phil Sharpe]

I think she means that if your full email address is in cell C3 then putting
then formula:
=MID(C3, SEARCH(".", C3) + 1, LEN(C3) - SEARCH(".",C3))
into D3 (for example) will give you the result you want.

If this is the case then it won't quite work for your example, because you
have a "." between john and smith.
If that's the case then try this:
=MID(C3,SEARCH(".",C3, SEARCH("@", C3))+1,LEN(C3)-SEARCH(".", C3,
SEARCH("@", C3, SEARCH("@", C3))))
it will start chopping text after the 1st "." that comes after the 1st "@"

HTH,
Phil

 

[Jammie]

Hi Phil
Thanks very much for your help!
Jammie

I think she means that if your full email address is in cell C3 then putting
then formula:
=MID(C3, SEARCH(".", C3) + 1, LEN(C3) - SEARCH(".",C3))
into D3 (for example) will give you the result you want.

If this is the case then it won't quite work for your example, because you
have a "." between john and smith.
If that's the case then try this:
=MID(C3,SEARCH(".",C3, SEARCH("@", C3))+1,LEN(C3)-SEARCH(".", C3,
SEARCH("@", C3, SEARCH("@", C3))))
it will start chopping text after the 1st "." that comes after the 1st "@"

HTH,
Phil