I have a bunch of URls, and I have to extract flavour details (by excel formula or provide other logic):

• recipes/caprese-chicken-foil-pack/2ac1725a-72b9-4ff1-9337-1d8c809b5db0/
• /recipes/cheesy-chicken-sausage-and-potatoes-foil-pack/298f643a-84ac-4a7e-8d59-912c2dec05c3/
• /recipes/cheesy-chicken-sausage-and-potatoes-foil-pack/298f643a-84ac-4a7e-8d59-912c2dec05c3/
• /recipes/grilled-banana-boats/09e8e5bb-c44e-4111-8af1-8b1cf8a182f5/
• /recipes/seasoned-sage-grilled-potato-pack/2e6d2280-b47b-468f-943d-eb87a37cf709/

My formula is: =IFERROR(MID(B12;FIND("/";B12;2) 1;FIND("foil";B12;1)-FIND("/";B12;2)-2);MID(B12;FIND("/";B12;2) 1;FIND("pack";B12;1)-FIND("/";B12;2)-2))

But it fail when I have foil or packs at the end

Would you provide with ideas? Thanx

CodePudding user response：

Try:

Formula in B1:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,"/","</s><s>")&"</s></t>","//s[preceding::*[1]='recipes']")&"-pack-foil-",LEFT(X,MIN(FIND({"-pack-","-foil-"},X)-1)))

More on FILTERXML() here.

CodePudding user response：

Step by step to get the recipes names: urls in column A In cell B1 =RIGHT(A1,LEN(A1)-7-FIND("recipes/",A1,1)) In cell C1 =LEFT(B1,FIND("/",B1,1)-1) In cell D1 =SUBSTITUTE(C1,"-"," ")