Parse ASIN's and add Hyperlink

Question

Say I get a string like this B01AVAF3AK-40 / B01AVAF5Q2-36. I would like to copy over any ASIN's into new cells on parallel columns.

I have been using a combination of =REGEXEXTRACT(A2, "(B[a-zA-Z0-9]{9}).*(B[a-zA-Z0-9]{9})") and =HYPERLINK(CONCATENATE("https://www.amazon.com/dp/product/",C2),C2).

The problem with this approach is: 1) I can only get two ASIN's from one string. 2) I don't have a single formula that does both regex matching and adds a hyperlink. How would I solve this in Google Sheets?

Additionally how would this be done using Excel?

My spreadsheet

I won't always have the same amount of ASIN's in the input string. Anywhere between 1 and 5. — Avi Kaminetzky, Aug 03 '17 at 18:21

Avi Kaminetzky · Accepted Answer · 2017-08-04T18:13:32.427

1

This works perfectly with Google Sheets (added solution to spreadsheet):

=ARRAYFORMULA(HYPERLINK("https://www.amazon.com/dp/product/" & REGEXEXTRACT(SPLIT(A2, "/"), "(B[a-zA-Z0-9]{9})"), REGEXEXTRACT(SPLIT(A2, "/"), "(B[a-zA-Z0-9]{9})")))

edited Aug 04 '17 at 18:13

answered Aug 03 '17 at 23:31

Avi Kaminetzky

1,489
2
19
42

in google sheets. problem is it will only split with a "/". – Avi Kaminetzky Aug 04 '17 at 18:12

Parse ASIN's and add Hyperlink

1 Answers1