Extract domain from text: Difference between revisions
No edit summary Tag: wikieditor |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 6: | Line 6: | ||
Use Google Spreadsheet [https://support.google.com/docs/answer/3098244?hl=en REGEXEXTRACT] function | Use Google Spreadsheet [https://support.google.com/docs/answer/3098244?hl=en REGEXEXTRACT] function | ||
<pre> | <pre> | ||
=REGEXEXTRACT( | =REGEXEXTRACT(A2, "(http[s]?\://[^/]+)") | ||
</pre> | </pre> | ||
Input: | Input text: | ||
<pre> | <pre> | ||
Yahoo! News https://tw.news.yahoo.com/abc | Yahoo! News https://tw.news.yahoo.com/abc | ||
| Line 20: | Line 20: | ||
Explanation: | Explanation: | ||
* Domain refers to text that starts with <nowiki>http://</nowiki> or <nowiki>https://</nowiki>, followed by multiple characters that are not the symbol {{kbd | key = <nowiki>/</nowiki>}}: {{kbd | key = <nowiki>[^/]+</nowiki>}}. = | * Domain refers to text that starts with <nowiki>http://</nowiki> or <nowiki>https://</nowiki>, followed by multiple characters that are not the symbol {{kbd | key = <nowiki>/</nowiki>}}: {{kbd | key = <nowiki>[^/]+</nowiki>}}. | ||
If the preference is to not include the schema (<nowiki>http://</nowiki> or <nowiki>https://</nowiki>) in the output, wrap the domain portion in a capture group instead: | |||
<pre> | |||
=REGEXEXTRACT(A2, "http[s]?\://([^/]+)") | |||
</pre> | |||
Input text: | |||
<pre> | |||
Example domain https://www.example.com/ | |||
</pre> | |||
Output: | |||
<pre> | |||
example.com | |||
</pre> | |||
Explanation: | |||
* REGEXEXTRACT returns the first capture group when one is present, rather than the whole match. Here the schema (<nowiki>http[s]?\://</nowiki>) is matched but left outside the parentheses, while only the domain part {{kbd | key = <nowiki>[^/]+</nowiki>}} is enclosed in {{kbd | key = <nowiki>(...)</nowiki>}}, so only the domain is returned. | |||
== Data Validation: Does the article content contain a domain == | |||
The original data includes domains, but the domains don't include http prefix, e.g., tw.news.yahoo.com or www.bbc.co.uk. Using Google Spreadsheet [https://support.google.com/docs/answer/3098292?hl=en REGEXMATCH] function, if it matches the regular expression rules, it returns TRUE. If not, it returns FALSE. {{exclaim}} The following syntax doesn't handle [https://en.wikipedia.org/wiki/IPv4 IPv4] format domains. (If the domain includes http prefix, you can directly search for: regular expression extract host) | |||
<pre> | |||
=IF(ISERROR(REGEXMATCH(A1, "([a-zA-Z0-9\-''\\.''~\:\/\?#\[\]@\!\$&'\(\)\''\+,;\=%]+\.[a-zA-Z]{2,}$|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})")), FALSE, REGEXMATCH(A1, "([a-zA-Z0-9\-''\\.''~\:\/\?#\[\]@\!\$&'\(\)\''\+,;\=%]+\.[a-zA-Z]{2,}$|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})")) | |||
</pre> | |||
Input 1: | |||
<pre> | |||
www.bbc.co.uk | |||
</pre> | |||
Output 1: | |||
<pre> | |||
TRUE | |||
</pre> | |||
Input 2: | |||
<pre> | |||
# 0.0.0 | |||
</pre> | |||
Output 2: | |||
<pre> | |||
TRUE | |||
</pre> | |||
Input 3: | |||
<pre> | |||
Yahoo! News | |||
</pre> | |||
Output 3: | |||
<pre> | |||
FALSE | |||
</pre> | |||
Other methods not recommended: | |||
* Checking if the domain ends with .com, .tw, .net, .org is inefficient because there are too many to enumerate. | |||
[[Category: Regular expression]] [[Category: Data Science]] [[Category: String manipulation]] | [[Category: Regular expression]] [[Category: Data Science]] [[Category: String manipulation]] | ||
Latest revision as of 19:16, 15 September 2026
Extract the domain part from URLs in article content
Using Google Sheets to extract domains[edit]
Use Google Spreadsheet REGEXEXTRACT function
=REGEXEXTRACT(A2, "(http[s]?\://[^/]+)")
Input text:
Yahoo! News https://tw.news.yahoo.com/abc
Output:
https://tw.news.yahoo.com
Explanation:
- Domain refers to text that starts with http:// or https://, followed by multiple characters that are not the symbol /: [^/]+.
If the preference is to not include the schema (http:// or https://) in the output, wrap the domain portion in a capture group instead:
=REGEXEXTRACT(A2, "http[s]?\://([^/]+)")
Input text:
Example domain https://www.example.com/
Output:
example.com
Explanation:
- REGEXEXTRACT returns the first capture group when one is present, rather than the whole match. Here the schema (http[s]?\://) is matched but left outside the parentheses, while only the domain part [^/]+ is enclosed in (...), so only the domain is returned.
Data Validation: Does the article content contain a domain[edit]
The original data includes domains, but the domains don't include http prefix, e.g., tw.news.yahoo.com or www.bbc.co.uk. Using Google Spreadsheet REGEXMATCH function, if it matches the regular expression rules, it returns TRUE. If not, it returns FALSE. 
The following syntax doesn't handle IPv4 format domains. (If the domain includes http prefix, you can directly search for: regular expression extract host)
=IF(ISERROR(REGEXMATCH(A1, "([a-zA-Z0-9\-''\\.''~\:\/\?#\[\]@\!\$&'\(\)\''\+,;\=%]+\.[a-zA-Z]{2,}$|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})")), FALSE, REGEXMATCH(A1, "([a-zA-Z0-9\-''\\.''~\:\/\?#\[\]@\!\$&'\(\)\''\+,;\=%]+\.[a-zA-Z]{2,}$|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"))
Input 1:
www.bbc.co.uk
Output 1:
TRUE
Input 2:
# 0.0.0
Output 2:
TRUE
Input 3:
Yahoo! News
Output 3:
FALSE
Other methods not recommended:
- Checking if the domain ends with .com, .tw, .net, .org is inefficient because there are too many to enumerate.