1

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.

5
  • 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) Feb 20, 2013 at 15:17
  • Any idea @CharlieRB ? Feb 20, 2013 at 15:24
  • @CharlieRB this is from superuser.com/questions/554130/…
    – Peter L.
    Feb 20, 2013 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, 2013 at 15:38
  • @CharlieRB this is just to answer "What have you tried?" ;)
    – Peter L.
    Feb 20, 2013 at 15:40

2 Answers 2

6

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)

4
  • Works as smooth and error free like a butter. Thanks again, Peter! :) Feb 20, 2013 at 16:10
  • Happy to help!)
    – Peter L.
    Feb 20, 2013 at 17:30
  • 1
    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, 2013 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, 2013 at 21:17
0

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)

You must log in to answer this question.

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