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]] | ||