Is there a way in Google Sheets to extract country level codes (if available) from domains?-CodePudding

I have a list of domains and I want to extract country codes from the URLs, if they are available. Is there a formula that I can use in Google sheets or Excel to quickly get this?

Expected output from the domains are mentioned below:

mk.ru -> ru
news.yahoo.co.jp -> jp
nabdn.com -> NA
247.libero.it -> it
zazoom.it -> it
news.goo.ne.jp -> jp
tw.news.yahoo.com -> tw
topics.smt.docomo.ne.jp -> jp
excite.co.jp -> jp

I tried to split the columns but it is not consistent and is manual to collate the country codes.

CodePudding user response：

try:

=INDEX(REGEXEXTRACT(REGEXREPLACE(A1:A9, "\b(co|ne|mk)\b", " "), "\b.{2}\b"))

CodePudding user response：

Use this

=ArrayFormula(LAMBDA(x,y, IF(x<>"com",x,IF(LEN(y)=2,y,NA())))
 (BYROW(A2:A, LAMBDA(x, IF(x="",,
  INDEX(SPLIT(x,"."), ,COLUMNS(SPLIT(x,".")))))),
  BYROW(A2:A, LAMBDA(x, IF(x="",,INDEX(SPLIT(x,"."), ,1))))))