0

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

Andriy Berezovskiy
  • 1
  • 1

2 Answers2

1

Try:

enter image description here

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.

JvdV
  • 70,606
  • 8
  • 39
  • 70
0

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,"-"," ")

guest34
  • 1