Suppose I have values in a coloumn as:
mail.google.com,
m.kilo.keepay.excel.com,
zero.one.seven.eight.xyz.com
I want the output like
google.com, excel.com and xyz.com
Please help me with the query.
Suppose I have values in a coloumn as:
mail.google.com,
m.kilo.keepay.excel.com,
zero.one.seven.eight.xyz.com
I want the output like
google.com, excel.com and xyz.com
Please help me with the query.
Assuming your input starts from A1
and down, use this formula in B1
and autofill:
=IFERROR(RIGHT(A1,LEN(A1)-SEARCH("#",SUBSTITUTE($A1,".","#",LEN($A1)-LEN(SUBSTITUTE($A1,".",""))-1))),A1)
This will handle google.com
strings as well - these which are top-level domains as it is)
You can create custom function in you excel file (described how to do it here http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx)
function like this:
Function TrimURL(url As String)
s = StrReverse(url)
d1 = InStr(s, ".")
If d1 = 0 Then
TrimURL = url
Exit Function
End If
d2 = InStr(d1 + 1, s, ".", vbTextCompare)
If d2 = 0 Then
TrimURL = url
Exit Function
End If
r = Left(s, d2 - 1)
TrimURL = StrReverse(r)
End Function
and then use it in cell like that:
=TrimURL(A1)