如何修正根域名提取公式？解决多后缀域名提取错误问题

Ah, I get the problem! Your existing formula uses a regex that grabs the last two dot-separated segments of the cleaned URL, which works for standard domains like example.com, but fails for cases where the suffix itself has multiple parts (like co.uk, gov.au, etc.). These are called public suffixes, and we need to handle them differently to get the full root domain (e.g., countryandtownhouse.co.uk instead of just co.uk).

Here are two practical solutions tailored to your use case (I assume you're using Google Sheets since you're using regexextract/regexreplace):

Solution 1: Regex Adjustment for Known Multi-Suffixes #

If you only deal with a limited set of common multi-suffixes, you can tweak your regex to prioritize matching those full suffix combinations first, then fall back to standard domains.

Updated Formula #

=regexextract(
  regexreplace(regexreplace(A1, "^[^/]//", ""), "/$", ""),
  "([^.]+\.(co|org|gov|com)\.(uk|au|hk))$|([^.]+\.[^.]+)$"
)

How It Works #

• The first part ([^.]+\.(co|org|gov|com)\.(uk|au|hk))$ matches root domains paired with common multi-suffixes (e.g., countryandtownhouse.co.uk, example.gov.au).
• The second part ([^.]+\.[^.]+)$ acts as a fallback for standard domains (e.g., example.com, test.org).
• You can expand the parentheses to include more suffix combinations you encounter (like net.uk, edu.jp, etc.).

Solution 2: Custom Function for Full Public Suffix Support #

For a more universal solution that handles almost all public suffixes (including less common ones), you can use a Google Apps Script custom function. This leverages a curated list of public suffixes to accurately identify where the root domain starts.

Step-by-Step Setup #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Replace the default code with this script:

function getRootDomain(url) {
  // Clean the URL by stripping protocol (http://, https://) and path
  const cleanedUrl = url.replace(/^[^\/]+\/\//, '').replace(/\/.*$/, '');
  
  // Simplified public suffix list (extend this with more entries as needed)
  const publicSuffixes = new Set([
    'co.uk', 'org.uk', 'gov.uk', 'net.uk', 'edu.au', 'gov.au', 'com.au',
    'com.hk', 'net.hk', 'org.hk', 'co.jp', 'ne.jp', 'org.jp', 'com.cn',
    'net.cn', 'org.cn'
  ]);

  const domainParts = cleanedUrl.split('.');
  
  // Check from the end to find the longest matching public suffix
  for (let i = domainParts.length - 1; i >= 1; i--) {
    const currentSuffix = domainParts.slice(i).join('.');
    if (publicSuffixes.has(currentSuffix)) {
      return domainParts.slice(i - 1).join('.');
    }
  }
  
  // If no multi-suffix matches, return the last two parts (standard domain)
  return domainParts.slice(-2).join('.');
}

3. Save the script (name it something like RootDomainTools), then return to your sheet.
4. Use the function like this: =getRootDomain(A1)

Bonus Tip #

You can keep the public suffix list updated by referring to Mozilla's official Public Suffix List (though for simplicity, the script above uses a common subset).

内容的提问来源于stack exchange，提问作者Ian