Extract Specific Data From URLs

[censo]

Hi all,

I'm interested in knowing if there's functions where you can extract specific data (item number information that may be numeric or alphanumeric) from URLs. The URL structure differs from one retailer to another so I'd be okay if individual functions are custom tailored for specific retailers (can be easily sorted out).

Here's a visual representation of what I'd like to accomplish:

For Wayfair:

1st type of URL:
Data: http://www.wayfair.com/12-Cup-Brewst...0-HMB1637.html
Output: HMB1637

2nd type of URL:
Data: http://www.wayfair.com/4.0-Cu.-Ft.-C...1466.html?PiID[]=14213412
Output: HER1466

* In both cases, extracted content appears after the last hypen (-) and before the '.html' and can vary in length depending on the item #
If the same function can be used for both types of URL, that'd be awesome


For Costco

Data: http://www.costco.com/Outland-24%22-...100222806.html
Output: 100222806

* the extracted content appears after the 2nd to last period (.) and before the '.html' and can vary in length depending on the item #


For Overstock:

Data: http://www.overstock.com/Bedding-Bat...7/product.html
Output: 7440617

* extracted content appears after the last 2nd to last forward slash (/) and before the last forward slash (/) and can vary in length depending on the item #


For Mill's Fleet Farm

Data: http://www.farmandfleet.com/products...ol-holder.html
Output: 581151

*extracted content appears after the last forward slash (/) and before the hypen (-)


For Sam's Club

Data: http://www.samsclub.com/sams/7-5ft-h...rod18310195.ip
Output: 18310195

* extracted content appears after the last forward slash (/), does NOT include 'prod' and before the '.ip'

Thanks

[Pete_UK]

You can see that the forum software has truncated those URLs, and so it is difficult to see the data that you want to extract in some cases. It might be easier to see if you put some URLs in an Excel file in one column and in another column tell us what you would want to extract from the URL. The FAQ describes how to attach a file to one of your posts (basically, click Go Advanced, then scroll down to Manage Attachments, then follow the on-screen prompts).

Pete

[censo]

Great idea Pete. How's this?

[censo]

And actual file attached if it makes it easier

[AlKey]

There is not much of consistency in this data-set so it may not be perfect.

try this array formula in B2 and copy down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

	A	B
1	Data
2	http://www.wayfair.com/12-Cup-Brewstation-Coffee-Maker-47900-HMB1637.html	HMB1637
3	http://www.wayfair.com/4.0-Cu.-Ft.-Compact-Refrigerator-with-freezer-HC40SG42S-HER1466.html?PiID	HER1466
4
5	http://www.costco.com/Outland-24%22-Portable-Propane-Fire-Bowl-Mega.product.100222806.html	100222806
6
7	http://www.overstock.com/Bedding-Bath/Super-Size-PrimaLoft-400-Thread-Count-Supima-Down-Alternative-Comforter/7440617/product.html	7440617
8
9	http://www.farmandfleet.com/products/581151-rubbermaid-fasttrack-power-tool-holder.html	581151
10
11	http://www.samsclub.com/sams/7-5ft-hallmark-sugar-dotcom-2015/prod18310195.ip	18310195

Sheet: Sheet1

[censo]

Alkey, thanks for your response, much appreciated.

I knew there wasn't consistency from one supplier URL to another, that's why I separated them out. Possible to build functions for each set of suppliers listed above so that I do get perfect results? (a bit more work I understand but I'd be deeply appreciative).

[FlameRetired]

censo,

@ how many unique supplier URLs are there?

Perhaps something like this? Line breaks at each choice.

Please Login or Register  to view this content.

With a lookup table in Y1:Z5 this helper column formula would go in O2 and filled down.

Please Login or Register  to view this content.

It's only function is to assign the first argument to the CHOOSE formula above.

It could probably be trimmed down somewhat.