Excel sort column by domain name, from end to begin

Question

I've seen a lot of answers on how to sort email list by domain. What I need is different. All I have is a list of domains and sub-domains that need to be sorted properly. As you may know, a domain hierarchy begins at the end, meaning first comes the .com, then the domain and then the suffix, e.g. "www" So, I need to group them by relevance. For example, I have the following domains:

www.somedomain.com
www.someotherdomain.co.uk
www.yetonemoredomain.org
sub.somedomain.com
1.somedomain.com
2.yetonemoredomain.org
3.someotherdomain.co.uk.
www2.yetonemoredomain.org
mail.someotherdomain.co.uk

Plain sort from A to Z will start from left to right, but I need it to be sorted from right to left, with the dot as a field separator. I know a simple bash command can do it but I need it in Excel. I was thinking about using "Text to columns" and separating by dots and then sorting columns as needed, but this may also cause some troubles with columns creation for 4th level domains and higher, such as x.y.x.somedomain.com. Columns will be created from left to right, this means the .com may fall into different columns instead of being the rightest column. I'm sure there must be a better way for doing this. Thanks in advance for your help. Ziv

Do you need a standard excel column solution, or is an VBA-function (defined in your macro-enabled workbook) a workable solution? — Prebsus, Nov 30 '17 at 11:22
I prefer not to have to deal with VBA and macros as I'm trying to create a template and saving a macro enabled template is not what I would like. I prefer a straight forward standard excel solution but the more I dig into it the stronger my feeling there isn't a way. I managed to do something by first splitting column by delimiter, then sort all columns by a given order, e.g. column 4 then column 3 and so on. Once sorted, I concatenated the columns to a single one adding a dot between them to get the "full" domain list sorted as desired. — TuxSax, Nov 30 '17 at 13:54

Vityata · Answer 1 · 2017-11-30T11:38:29.953

This is probably a variation of one of the oldest algorithmic problems, known as Reverse the ordering of words in a string. The expected answer is the following - reverse twice - each word and then the whole sentence.

In your case, you need to do exactly this and then to sort.

Public Function ReverseMe(textToReverse As String, _
        Optional delim As String = " ") As String

    Dim test    As String
    Dim arr     As Variant
    Dim arr2    As Variant
    Dim arrPart As Variant
    Dim cnt     As Long

    arr = Split(textToReverse, delim)
    ReDim arr2(UBound(arr))

    For Each arrPart In arr
        arr2(cnt) = StrReverse(arrPart)
        cnt = cnt + 1
    Next arrPart

    ReverseMe = StrReverse(Join(arr2, delim))

End Function

Public Sub TestMe()

    Debug.Print ReverseMe("veso.dosev.diri.rid", ".")
    Debug.Print ReverseMe("VBA is the best language")

End Sub

You would get:

rid.diri.dosev.veso
language best the is VBA

Excel sort column by domain name, from end to begin

1 Answers1