I want to obtain root domains from a list of URLs with REGEXEXTRACT but want the list of all possible TLDs to come from another sheet, to avoid specifying com|net|org|all other by hand. Basic idea:

  =REGEXEXTRACT(A2, "\w+\.(?:com|net|org)")

What I want:


Any way to achieve this?

  • This REGEX is limited to the sub domain www. Should it cover more than that? – Jacob Jan Tuinstra Feb 24 '15 at 12:10
  • It extracts root domain (domain.com) from full URL. – dzhi Feb 24 '15 at 12:12

You need to do this.


new Google Sheet


old Google Spreadsheet


For a range you need to do this.



| improve this answer | |
  • No, i want to get list of all TLDs from other sheet and use that instead of specifying them in com|net|org fashion. – dzhi Feb 24 '15 at 12:23
  • @purpler Would that yield the same result? – Jacob Jan Tuinstra Feb 24 '15 at 12:25
  • That's what I'm trying to figure out. Logic said it would but i failed to get it working, probably due to lack of understanding of Sheets formula syntax. – dzhi Feb 24 '15 at 12:27
  • @purpler Perhaps based on your locale, try this: =ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("URL_OR_KEY"; "A2:A14"); "\w+\.(?:com|net|org)")) – Jacob Jan Tuinstra Feb 24 '15 at 12:36
  • 2
    You dont seem to understand what I'm trying to achieve. I want to import all possible TLDs (instead of just com, net, org) from other sheet or document and use them for regex. – dzhi Feb 24 '15 at 12:40

Guessing that A2 contains something like https://webapps.stackexchange.com and that another sheet contains something like: A2 com, A3 net, A4 org then in say B1:

=TEXTJOIN("|",1,IMPORTRANGE(" k e y ","A2:A"))

should return something like com|net|org provided permission is granted.

This may be applied within a REGEXEXTRACT formula like so:


to return, for the example: stackexchange.com (a link to https://stackexchange.com/) and for https://www.draw.io/ return #N/A.


| improve this answer | |

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.