CODEWITHSUNDEEP
excelcolumnsorting
TuxSax
TuxSax

Reputation: 1055

Excel sort column by domain name, from end to begin

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

Upvotes: 0

Views: 395

Answers (1)

Vityata
Vityata

Reputation: 43593

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

Upvotes: 2

Related Questions