Extracting Info from URL using Google Sheets

user_15608584 0
Viewed 51

I would like to take a URL and extract characters to multiple cells in Google Sheets.

The format of these URLs are:

https://www.sideshow.com/collectibles/{PRODUCT_NAME}-{PRODUCT_ID}

This is an example of the URL:

I'd like to extract the following to 2 different cells:

  • Product ID: 300485 written to cell A1 as 300485
    This 6 digit number will change on every URL but it will always be the last 6 characters of the URL

  • Product Name: marvel-scarlet-witch written to cell B1 as Marvel Scarlet Witch (no dashes, proper case format)

The Product Name, marvel-scarlet-witch, will change with every URL but its position in the URL is constant.

I'm not sure if all this can be done in a single action or if two separate ones would be required.

google-sheets
2 Answers

Alternatively you can also try (assuming string in D1)

=split(proper(substitute(regexreplace(D1, ".*\/(.*?)-side.*?([^-]\d+)$", "$2✓$1"), "-", " ")), "✓")

Change range to suit.

See this link for a brief explanation about the regular expression.

enter image description here

user_38894230

Assuming that the string is in cell C1.

To extract Product ID from the given URL format, use the formula on A1 cell:

=PROPER(SUBSTITUTE(REGEXEXTRACT(C1,".*/(.*)-sideshow-collectibles"), "-", " "))

To extract Product Name from the given URL format, use the formula on B1 cell:

=RIGHT(C1,6)
user_74525480
Related