Advanced ordering of columns

[nifiction]

I have a list of domain names ending in different country-extensions

*domain1.org
*domain2.net
*domain3.com
*domain4.co.uk
*domain5.co.id
*domain6.com
*domain7.co.uk
*domain8.co.id

All these domains are in one column but I need to find a way to sort the according to their extension, with all extensions being grouped. I tried searching for "something".org (find: ~.org --> replace with: .org) and then replacing it with "nothing".org but could not get it working. Any ideas?

Thanks a mil!

 

[MrKowz]

Hmm - one thought would to use a helper column. Try in the column next to it, enter the following formula:

=RIGHT(A1,LEN(A1)-FIND(".",A1))

copy that down

sort the helper column, then hide the helper column.

 

[nifiction]

Thx Keith,

I cannot get the formula to work however. Completely just copy-pasted as you suggested, gives me an error (I might have to replace parts of the fomula but since I have never used such a "complicated" one, I don't really understand it... sorry for the greenhornness ).

I put the values in column A, starting at A1. I then pasted the formula you suggested in B1 which resulted in the error.

 

[MrKowz]

There shouldn't be any error, I tried it on my end and it worked fine. What error are you getting?

 

[nifiction]

just that my formula contains an error ("the formula you entered contains an error") and that I should fixed it. Clicking help leads me to the standard non-specific help on formula of Excel. I am using the same version (Excel 2003 SP2) of Excel as you are...

Do I really just have to copy-paste it as you put it on there? Not vary the LEN(A1)-FIND(".",A1) part somehow?

 

[MrKowz]

that is correct. Try manually typing it in. Here is an explanation of the formula:

=RIGHT(Cell, Number) grabs the Right-most number of characters from a cell.

LEN(cell) returns the total number of characters from a cell

FIND(character,cell) returns the position of the desired character within a cell.

So combined, LEN(A1)-FIND(".",A1) will perform an operation that looks for the length of the text in A1, it will then find the first "." (without the quotes) in A1, and return that position. The result is the number of characters that are to the right of the first "."

Adding that into the RIGHT function:

=RIGHT(A1,LEN(A1)-FIND(".",A1)) will look in cell A1, and return all characters to the right of the first "."

 

[nifiction]

Good morning,

I figured out the "problem". In my version of Excel, I have to use ";" (semi-colons) to separate the different parts of the formula rather than "," (kommas). I guess that has something to do with keyboard or language settings of my OS?! Never mind, worked like a charm.

Thanks! Outstandingly quick and competent help!

 

[MrKowz]

Ahh ok. I guess I should put my Excel localization in my sig too.

 

[nifiction]

haha... ah well... if you help them silly Europeans out they shouldn't complain if they don't get it right :P