Parsing string from URL

[jdkuehne]

In Excel, I need to pull part numbers from the URLs in a site map. The URLs look something like this:

http://www.mysite.com/shop/catalog/...egory3/category4/item/partnumber1/part-title1
http://www.mysite.com/shop/catalog/category1/category2/category3/item/partnumber2/part-title2
http://www.mysite.com/shop/catalog/category1/category2/item/partnumber3/part-title3
http://www.mysite.com/shop/catalog/...egory3/category4/item/partnumber4/part-title4

I need to grab the string between the "item/" and the next "/" so that the results look like this:

partnumber1
partnumber2
partnumber3
partnumber4

There can be any number of "/" before the string and any number after. The key thing is that the string to parse comes immediately after "item/" and ends before the very next "/"

I've found a number of solutions on this site that address similar situations but none seem to work for this pattern. I'm not a programmer, so I'm not sure how to approach this. The closest I've come is a solution that will start at the correct spot but then includes everything to the end of the URL.

Any help is appreciated.

 

[jtakw]

Hi,

Try this, formula copied down:

Book1
	A	B
1	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber1/part-title1	partnumber1
2	http://www.mysite.com/shop/catalog/category1/category2/category3/item/partnumber2/part-title2	partnumber2
3	http://www.mysite.com/shop/catalog/category1/category2/item/partnumber3/part-title3	partnumber3
4	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber4/part-title4	partnumber4
Sheet341

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("item/",A1)+5,99),"/",REPT(" ",99)),99))

 

[jdkuehne]

Thanks, jtakw. I don't understand it, but it works perfectly. Is there a way to trap the condition where the "item/" string doesn't exist and return something other than "#VALUE!"?

 

[jtakw]

Yes, just add IFERROR:

Book1
	A	B
1	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber1/part-title1	partnumber1
2	http://www.mysite.com/shop/catalog/category1/category2/category3/item/partnumber2/part-title2	partnumber2
3	http://www.mysite.com/shop/catalog/category1/category2/category3/none/nopartnumber/part-title2
4	http://www.mysite.com/shop/catalog/category1/category2/item/partnumber3/part-title3	partnumber3
5	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber4/part-title4	partnumber4
Sheet341

Cell Formulas
Range	Formula
B1	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("item/",A1)+5,99),"/",REPT(" ",99)),99)),"")

 

[jdkuehne]

Beautiful - I even understand it.
Thanks for the speedy reply!

 

[jtakw]

You're welcome, welcome to the forum.

 

[Scott Huish]

You could also make sure that it doesn't fail:
=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("item/",A1&"item/")+5,99),"/",REPT(" ",99)),99))

Excel 2010
	A	B
1	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber1/part-title1	partnumber1
2	http://www.mysite.com/shop/catalog/category1/category2/category3/item/partnumber2/part-title2	partnumber2
3	http://www.mysite.com/shop/catalog/category1/category2/category3/none/nopartnumber/part-title2
4	http://www.mysite.com/shop/catalog/category1/category2/category3/category4/item/partnumber4/part-title4	partnumber4
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,SEARCH("item/",A1&"item/")+5,99),"/",REPT(" ",99)),99))
B2	=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("item/",A2&"item/")+5,99),"/",REPT(" ",99)),99))
B3	=TRIM(LEFT(SUBSTITUTE(MID(A3,SEARCH("item/",A3&"item/")+5,99),"/",REPT(" ",99)),99))
B4	=TRIM(LEFT(SUBSTITUTE(MID(A4,SEARCH("item/",A4&"item/")+5,99),"/",REPT(" ",99)),99))

 

[jdkuehne]

Thanks, Scott, for the alternate solution. I think I prefer jtakw's in this instance because it allows the error condition to be replaced by any string, which should be useful for my end purpose.