CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Banana
Banana

Reputation: 1

how to remove a set of digits from string in Google Sheets

I'm working on an htaccess file in Google Sheets. Now I have all these new URL's with a product ID in it. Like: http://sample.com/catogory/22265/product/ That number (in this case 22265) is different from every other URL, as it is a unique ID.

Is there a way to remove that group of digits including 1 slash (/) from the string? So like this: http://sample.com/catogory/product/?

Upvotes: 0

Views: 3544

Answers (2)

player0
player0

Reputation: 1

use:

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

or array fx variant:

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

0

or shorter:

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

0

_____________________________________________________________

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))); " "; "/"); ":/"; "://"))

enter image description here

Upvotes: 1

ADW
ADW

Reputation: 4247

You could try using regular expressions. Something like this: 

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

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

Upvotes: 1

Related Questions