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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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