0

in column B I have a string divided by the "|" delimiter. One and only one of the parts inside each "|" contains the word "Above". enter image description here In the example I shared the part inside "|" that contains the word "Above" is string5, because there are 4 "|" before it + 2 "|" after it. meaning: enter image description here I want a formula to get the answer string5 Would love to get your help.

Here the solution, credit to @JvdVL: =TRIM(MID(SUBSTITUTE([@[Image URL]],"|",REPT(" ",LEN([@[Image URL]]))),(LEN(LEFT([@[Image Caption]],FIND("Above",[@[Image Caption]])-1))-LEN(SUBSTITUTE(LEFT([@[Image Caption]],FIND("Above",[@[Image Caption]])-1),"|","")))*LEN([@[Image URL]])+1,LEN([@[Image URL]])))

Kobe2424
  • 147
  • 7
  • 1
    See if [this](https://stackoverflow.com/q/61837696/9758194) answers your question. – JvdV Jan 30 '21 at 17:54
  • Not really what I had in mind but if you solved your own problem that's great! – JvdV Jan 30 '21 at 18:31
  • I was on the right way but my formula was twice as long as the end result here, and this is much simpler. Cheers. – Kobe2424 Jan 30 '21 at 18:42
  • If you scroll down in the linked q&a you'll see it can be done even more effectively using Filterxml. – JvdV Jan 30 '21 at 18:44

0 Answers0