如何從 Google 表格中的字符串中刪除一組數字 (how to remove a set of digits from string in Google Sheets)

問題描述

如何從 Google 表格中的字符串中刪除一組數字 (how to remove a set of digits from string in Google Sheets)

我正在處理 Google 表格中的 htaccess 文件。現在我有了所有這些帶有產品 ID 的新 URL。比如：http://sample.com/cagory/22265/product/ 這個數字（在本例中為 22265）與其他所有 URL 不同，因為它是唯一的ID。

有沒有辦法從字符串中刪除包括 1 個斜杠 (/) 的那組數字？所以像這樣：http://sample.com/cagory/product/?

參考解法

方法 1:

You could try using regular expressions. Something like this:

=REGEXREPLACE(A1,"\d+\/","")

... where A1 has the url with the product ID.

方法 2:

use:

=REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A1:A; "[^\D+]"; ); "//"; "/"); ":/"; "://")

or array fx variant:

=ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A1:A; 
 "[^\D+]"; ); "//"; "/"); ":/"; "://"))

or shorter:

=ARRAYFORMULA(REGEXREPLACE(A1:A; "\d+\/"; ))

_____________________________________________________________

an alternative would be:

=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFERROR(
 SPLIT(A1:A; "/")); "select Col1,Col2,Col3,"&TEXTJOIN(","; 1; "Col"&ROW(
 INDIRECT("A5:A"&COLUMNS(SPLIT(A1:A; "/"))))); 0));;999^99))); " "; "/"); ":/"; "://"))

(by Banana、ADW、player0)

參考文件

1. how to remove a set of digits from string in Google Sheets (CC BY‑SA 2.5/3.0/4.0)