Extracting Info from URL using Google Sheets

Question

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:

• https://www.sideshow.com/collectibles/marvel-scarlet-witch-sideshow-collectibles-300485

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.

Answer 1

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)

Answer 2

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.