Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    
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
    
@CharlieRB this is just to answer "What have you tried?" ;) – Peter L. Feb 20 '13 at 15:40
up vote 6 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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .