Sort by TLD - excel hack

[joe]

Just thought I'd share this little tip for sorting a list of names with different TLDs in excel

=TRIM(MID(A1,FIND(".",A1)-0,7))

where A1 is the first cell with a domain name in it

paste that next to your list and you'll quickly be able to sort by TLD and end up with something similar to the following:

Hope you find it useful. If not, a full refund will be available on request

 

[Joel]

Nice one, thanks Joe

 

[sp@rky13]

Just wondering how that works seeing as it appears that you have a specific number in there. Wouldn't you just find the first . and then trim away everything to the left? (Assuming we're not dealing with sub domains). I'm a little confused with how you're doing it.

 

[sp@rky13]

For whatever reason, using your formula it didn't work but through a quick google search I created this instead: =RIGHT(A1,LEN(A1)-FIND(".", A1,1))

Assuming of course that A1 is where the domain name is. Also, fyi it means that the . is not kept in front of the TLD in the TLD column.

Oh and Joe, not trying show you up or anything, just enjoy practicing stuff/learning/researching stuff when I can.

 

[joe]

Nice one, thanks Joe

you're welcome

Just wondering how that works seeing as it appears that you have a specific number in there. Wouldn't you just find the first . and then trim away everything to the left? (Assuming we're not dealing with sub domains). I'm a little confused with how you're doing it.

Me too, but it works. There's probably a better way.

The "7" allows for up to seven characters, any less and you get output like .com.a instead of .com.au

It won't work on subdomains, as you say, it finds the first . and does it's thing from there.

For whatever reason, using your formula it didn't work

works for me...

but through a quick google search I created this instead: =RIGHT(A1,LEN(A1)-FIND(".", A1,1))

Assuming of course that A1 is where the domain name is. Also, fyi it means that the . is not kept in front of the TLD in the TLD column.

...also works for me

Oh and Joe, not trying show you up or anything, just enjoy practicing stuff/learning/researching stuff when I can.

WHY YOU LITTLE!! hahaha no problem. I wouldn't throw it out there if I was afraid of people picking it apart. Thanks for your contribution.