Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
What have you tried or researched so far? –  CharlieRB Feb 20 '13 at 15:12
    
I am able to fetch .com but failing to get whole domain name i.e. like xyz.com Query used: =RIGHT(A2,FIND(".",A2)-1) –  Developer by Blood Feb 20 '13 at 15:17
    
Any idea @CharlieRB ? –  Developer by Blood Feb 20 '13 at 15:24
    
@CharlieRB this is from superuser.com/questions/554130/… –  Peter L. Feb 20 '13 at 15:33
    
@PeterL. It might be the same person asking, but it's not a duplicate question. The previous was asking how to filter unique values. This one is asking how to trim the data in the field. –  CharlieRB Feb 20 '13 at 15:38
show 1 more comment

2 Answers

up vote 5 down vote accepted

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)

share|improve this answer
    
Works as smooth and error free like a butter. Thanks again, Peter! :) –  Developer by Blood Feb 20 '13 at 16:10
    
Happy to help!) –  Peter L. Feb 20 '13 at 17:30
    
Assuming it is URLs in the format above this is an awesome solution. Just watch out for tripart TLDs (e.g. bbc.co.uk or sbs.com.au). –  Gary Feb 20 '13 at 21:15
    
@Gary assuming the input this is ok, but tripods where warned about before: superuser.com/questions/554130/… thanks for the catch anyway! –  Peter L. Feb 20 '13 at 21:17
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.