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:

  =REGEXEXTRACT(A2, "\w+\.(?:IMPORTRANGE("$SHEETKEY",!A2:A))")

Any way to achieve this?

migrated from superuser.com Feb 24 '15 at 9:33

This question came from our site for computer enthusiasts and power users.

  • 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.

Formula

new Google Sheet

=REGEXEXTRACT(IMPORTRANGE("URL_IMPORT_SHEET", "A2"), "(\w+\.\w+)$")

old Google Spreadsheet

=REGEXEXTRACT(IMPORTRANGE("KEY_IMPORT_SHEET", "A2"), "(\w+\.\w+)$")

For a range you need to do this.

=ARRAYFORMULA(REGEXEXTRACT(IMPORTRANGE("URL_OR_KEY", "A2:A14"), "(\w+\.\w+)$"))

References

  • 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:

 =REGEXEXTRACT(A2,"\w+\.(?:"&B$1&")")

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

TEXTJOIN
IMPORTRANGE
REGEXEXTRACT

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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