Formula to Target Specific Section Within a URL

[censo]

Hello,

I have a semi-successful solution to my problem. The formula below extracts the item number from a URL but only when the URL is "clean". If the URL has text within it as shown in cell A3, I get incorrect results.

In the table below, the first output is correct but the second is not. Column C shows the correct desired output.

What modification to this formula would target everything present after the last forward "/" slash but before the ".ip"?

Excel 2013 32 bit
	A	B	C
2	https://www.samsclub.com/sams/118235.ip	SAM-118235	SAM-118235
3	https://www.samsclub.com/sams/oif-vertical-file-cabinet-4-drawer-economy-letter-26-1-2-black/prod5380361.ip	SAM-black/prod5380361	SAM-prod5380361
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2<>"","SAM-"&LEFT(TRIM(RIGHT(SUBSTITUTE(A2,"/sams/",REPT(" ",20)),20)),LEN(TRIM(RIGHT(SUBSTITUTE(A2,"/sams/",REPT(" ",20)),20)))-3),"")

Thanks

 

[Peter_SSs]

See if this solves your problem.

Excel Workbook
	A	B
2	https://www.samsclub.com/sams/118235.ip	SAM-118235
3	https://www.samsclub.com/sams/oif-vertical-file-cabinet-4-drawer-economy-letter-26-1-2-black/prod5380361.ip	SAM-prod5380361
Extract from URL

Also, if you are using one of the HTML makers suggested in this site (see link in my signature block below) you should update to the latest version & I think your screen shots will look a lot better.

 

[censo]

That did the trick, Peter. Thanks for your contribution and for the note about the updated HTML makers. I was wondering my table came out looking so funky, it's all good now!

Is there a way to mark this thread as SOLVED or is that not necessary?

 

[Peter_SSs]

That did the trick, Peter. Thanks for your contribution and for the note about the updated HTML makers. I was wondering my table came out looking so funky, it's all good now!

No problem, glad it helped.

Is there a way to mark this thread as SOLVED or is that not necessary?

What you have written is fine. It tells us you are satisfied, but it is not uncommon for a better solution to come along even after an OP says their problem is solved and we want to keep it that way.