Excel – Extracting Just a Product ID from a URL in bulk

Method 1.

If all the URLS are the same except for the productid, just use find and search function and replace all text with nothing except for the product id

Method 2.

if you can run Python on the file before loading into Excel, this code will extract the ProductId

with open('url.txt') as f:
    for url in f.readlines():
        print( url.split('/')[2].split('?')[0] )

Method 3.

In Excel

=LEFT(MID(A2,FIND(“productID.”,A2)+10,256),(FIND(“/”,MID(A2,FIND(“productID.”,A2)+10,256),1)-1))

Method 4

Use this spreadsheet on Google sheets:

https://docs.google.com/spreadsheets/d/1fFrPPPGdBN-D8abYXzQZ-YA-qeRVk3U7pNE-EF0pvo4/edit?usp=sharing

More info on this thread in Reddit:

Advertisements