Editing Extract url from text (section)

== 從文章內容，擷取完整網址 ==
=== 使用 Google sheet 擷取完整網址 ===

* (optional) Step1: [https://workspace.google.com/marketplace/app/extract_urls/143780651832 Extract URLs - Google Workspace Marketplace] "The application extracts links and converts them to the HYPERLINK formula" {{Gd}}
* (optional) Step2: Using the [https://support.microsoft.com/zh-tw/office/formulatext-%E5%87%BD%E6%95%B8-0a786771-54fd-4ae2-96ee-09cda35439c8 FORMULATEXT 函數 - Microsoft 支援服務]
* Step3: 使用 Google 試算表正規表示法 ([[Regular expression]]) 的 [https://support.google.com/docs/answer/3098244?hl=zh-Hant REGEXEXTRACT] 函數，從文章內容擷取第一個網址。
<pre>
=REGEXEXTRACT(A1, "(http[s]?://[a-zA-Z0-9\-_\\._~\:\/\?#\[\]@\!\$&'\(\)\*\+,;\=%]+)")
</pre>

詳細操作說明：[https://errerrors.blogspot.com/2023/10/how-to-quickly-extract-links-from-google-sheets.html 如何從 Google 試算表，快速取出連結]

=== 使用 Google sheet 刪除文章內網址 ===
Using [https://support.google.com/docs/answer/3098245?hl=zh-Hant REGEXREPLACE] function

<pre>
=REGEXREPLACE(A1, "(http[s]?://[a-zA-Z0-9\-_\\._~\:\/\?#\[\]@\!\$&'\(\)\*\+,;\=%]+)", "")
</pre>

=== 使用 Sublime Text 擷取完整網址 ===
使用 Sublime Text 等支援 regular expression 的文字編輯器
* 選單 Find --> Replace
* 啟用 Regular expression
* Find What: {{kbd | key= <nowiki>.*(http[s]?://[a-zA-Z0-9\-_\\._~\:\/\?#\[\]@\!\$&'\(\)\*\+,;\=%]+).*</nowiki>}} {{exclaim}} 此處輸入資料是一行只有一個網址。如果有多個網址，將會截取靠近行尾的網址。
* Replace with: {{kbd | key= <nowiki>\1</nowiki>}}

=== 使用 Microsoft Excel 擷取完整網址 ===
使用 Excel [https://support.office.com/en-us/article/find-findb-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628?ui=en-US&rs=en-US&ad=US FIND], [https://support.office.com/en-us/article/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb?ui=en-US&rs=en-US&ad=US LEN] 與 [https://support.office.com/en-us/article/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028?ui=en-US&rs=en-US&ad=US MID] 等函數。資料限制：網址前後需要間隔空白或換行符號。以下公式從 B2 儲存格擷取完整網址：(公式修改自 guitarthrower 提供的公式<ref>[https://stackoverflow.com/questions/25429211/extract-urls-from-a-cell-of-text-in-excel vba - Extract URL's from a Cell of Text in Excel - Stack Overflow]</ref>)

<pre>
=IF(ISERROR(MID(SUBSTITUTE(B2, "
", " "),FIND("http",SUBSTITUTE(B2, "
", " ")),IFERROR(FIND(" ",SUBSTITUTE(B2, "
", " "),FIND("http",SUBSTITUTE(B2, "
", " ")))-1,LEN(SUBSTITUTE(B2, "
", " ")))-FIND("http",SUBSTITUTE(B2, "
", " "))+1)), "", MID(SUBSTITUTE(B2, "
", " "),FIND("http",SUBSTITUTE(B2, "
", " ")),IFERROR(FIND(" ",SUBSTITUTE(B2, "
", " "),FIND("http",SUBSTITUTE(B2, "
", " ")))-1,LEN(SUBSTITUTE(B2, "
", " ")))-FIND("http",SUBSTITUTE(B2, "
", " "))+1))
</pre>

=== 測試資料 ===
輸入資料: 不包含 HTML 語法的 [http://www.w3schools.com/tags/att_a_href.asp a href] 屬性標籤
<pre>
Yahoo! 新聞 https://tw.news.yahoo.com/abc
</pre>

輸出資料:
<pre>
https://tw.news.yahoo.com/abc
</pre>

說明:
# 網址可能是 <nowiki>http://</nowiki> 或 <nowiki>https://</nowiki> 開頭，所以條件是 {{kbd | key = <nowiki>http[s]?://</nowiki>}}
# 根據 [http://tools.ietf.org/html/rfc3986/ RFC 3986] 的 [http://tools.ietf.org/html/rfc3986#section-2 Section 2: Characters] 網址允許的文字有 {{kbd | key = <nowiki>ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-._~:/?#[]@!$&'()*+,;=</nowiki>}}，其他文字則需要加上比例符號 % 編碼。 <ref>[http://stackoverflow.com/questions/1547899/which-characters-make-a-url-invalid validation - Which characters make a URL invalid? - Stack Overflow]</ref>